Ok, my English is terrible I'll try to describe the problem.
My script queries the database (MYSQL), bringing some data, including a date in "unix timestamp", which is converted to "datetime" in itself "SELECT" using the command "FROM_UNIXTIME()", but I have to separate the same field "unix timestamp" in two columns, one with the date, and another with the conversation hour.
How do I "format" field of the same table in different ways?
For u can understand better, my timestamp field in "SELECT" runs "FROM_UNIXTIME(timestamp, '%d/%m/%Y - %H:%i')", i just want to separate date and time.
I hope you understand Thanks.
| [reply] |
select
FROM_UNIXTIME('%Y:%M:%D'),
FROM_UNIXTIME('%h:%i:%s'),
other, fields, you want
... rest of query
See http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_from-unixtime | [reply]
[d/l] |
Ok, now, look at my code, in the first post.
In my select, i save the "timestamp" value in the var "$timestamp", and list it in "while", my question is, how to print this twice, 1 with date, and 1 with hour?
| [reply] |
Ok man, we are getting closer.
Now, my while is showing hour 2 times.
I'll send my actual code, thanks.
Here: http://www.flickr.com/photos/maicon1121/7176525646/in/photostream, a print of my script page, for u can understand.
#!C:\wamp\bin\perl\bin\perl.exe
BEGIN { use CGI::Carp "fatalsToBrowser"; }
use CGI qw(param);
use DBI;
use warnings;
use strict;
my $porpagina = param('porpagina');
print "Content-type: text/html \n\n";
print <<END
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1 Transitional//EN" "http:/
+/www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Netdeep Secure</title>
</head>
END
;
# Criei um objeto CGI
my $cgi = CGI->new;
# Carreguei o post numa variável e coloquei no sql das 2 consultas
my $var = $cgi->param("nome");
if (!$porpagina) {
$porpagina = 150;
}
# Conexão com o BD
my $dbh = DBI->connect('DBI:mysql:messenger;host=localhost', 'root', '
+'
) || die "Could not connect to database: $DBI::errstr";
# Faz uma segunda consulta no banco para obter os dados do registro
my $sth = $dbh->prepare("SELECT FROM_UNIXTIME(timestamp, '%d/%m/%Y'),
+FROM_UNIXTIME(timestamp, '%H:%i'), clientaddress, localid, remoteid,
+CONVERT(eventdata USING utf8) FROM messages WHERE localid LIKE '%$var
+%' ORDER BY id DESC LIMIT $porpagina");
$sth->execute( );
$sth->{LongReadLen}=500000;
$sth->{LongTruncOk}=1;
print qq|
<script type="text/javascript">
function somente_numeros(porpagina){
var campo = document.getElementById(porpagina).value;
var divide = campo.split("");
var quant = divide.length;
var permitidas = new Array (1,2,3,4,5,6,7,8,9,0);
var i, achou = 0;
var text = '';
for(i=0; i<10; i++){
if(divide[quant - 1] == permitidas[i]){
achou = 1;
}
}
if(achou == 0){
divide[quant - 1] = '';
}
for(i=0; i<quant; i++){
text += divide[i];
}
document.getElementById(porpagina).value = text;
}
function enviar(){
var campo = document.getElementById('nome').value;
if( campo != ''){
document.forms["pesquisa"].submit();
}else{
alert('O campo E-mail não pode ser vazio');
}
}
</script>
|;
print <<END
<body>
<div style="width:960px; height:30px; background:#dbdbdb;">
<h2 style="padding-left:15px;">Pesquisa</h2>
</div>
<br>
<form action="script.cgi" method="post" name="pesquisa" id="pesquisa"
+enctype="multipart/form-data">
E-mail:
<input name="nome" id="nome" type="text" size="30" style="margin-left:
+86px;" />
ex: contato\@empresa.com.br
<br><br>
Qtde. de registros:
<input type="text" name="porpagina" id="porpagina" size="5" style="mar
+gin-left:17px;" onkeyup="somente_numeros('porpagina')">
somente números
<br><br>
<input type="button" value="Buscar" onclick="enviar()" />
<input name="limpar" type="reset" value="Limpar" />
</form>
<br>
<div style="width:960px; height:30px; background:#dbdbdb;">
<h2 style="padding-left:15px;">Log</h2>
</div>
<table width="960">
<tr>
<th scope="col" width="10%">Data</th>
<th scope="col" width="10%">Hora</th>
<th scope="col" width="18%">Endereço IP</th>
<th scope="col" width="21%">De</th>
<th scope="col" width="23%">Para</th>
<th scope="col" width="23%">Mensagem</th>
</tr>
END
;
my $timestamp = '2012-05-11 06:55:36';
while ( my($timestamp, $timestamp, $clientaddress, $localid, $remoteid
+, $eventdata) = $sth->fetchrow_array() )
{
print "
<tr>
<td align='center'>$timestamp</td>
<td align='center'>$timestamp</td>
<td align='center'>$clientaddress</td>
<td align='center'>$localid</td>
<td align='center'>$remoteid</td>
<td align='center'>$eventdata</td>
</tr>"."";
}
print <<END
</table>
<div style="width:960px; height:30px; background:#dbdbdb;">
</div>
END
;
print <<END
</body>
</html>
END
;
$sth->finish();
$dbh->disconnect; undef $dbh;
| [reply]
[d/l] |