Log in

View Full Version : HELP! - Dynamically filling PHP Tabe with FORMATTED Date & Time


Romsky
08-06-2005, 10:07 PM
Here is the code that I am using:
-------------------------


//Connect to database

mysql_connect ( $dbhost, $dbuser, $dbpass)or die("Could not connect: ".mysql_error());
mysql_select_db($dbname) or die(mysql_error());



$tr_num = $_REQUEST['tr_num'];


$result = mysql_query("select * from pack_stat WHERE tr_num=$tr_num order by updated");


echo "
<html>
<body>
<center>
<table BORDER='1' CELLSPACING='1' CELLPADDING='0' WIDTH='585' bordercolordark='#333333' bordercolorlight='#666666'>
<thead>
<td WIDTH='70'>Tracking#</td>
<td WIDTH='50'>User</td>
<td WIDTH='170'>Status</td>
<td WIDTH='95'>Date</td>
<td WIDTH='50'>Time</td>
<td WIDTH='150'>Last Updated</td>
</thead>
";


while($r=mysql_fetch_array($result)){


$tr_num=$r["tr_num"];
$user=$r["user"];
$status=$r["status"];
$date=$r["date"];
$time=$r["time"];
$updated=$r["updated"];


echo "
<tr>
".$tr_num=$r["tr_num"]."</td>
<td>".$user=$r["user"]."</td>
<td>".$tstatus=$r["status"]."</td>

<td>".date("m/d/Y",$date=$r["date"])."</td>
<td>".date("H:i",$time=$r["time"])."</td>

<td>".$updated=$r["updated"]."</td>
</tr>
";

}


?>

-------------------------

Now every time the page loads ALL dates are 12/31/1969, and the time is always 16:00.

The data exists in the tabe as follows:

Date: "2005-08-06"
Time: "17:04:00"

I want it to look like
08/06/2005

17:04

I have been able to format the data but it won't dynamically fill the table.

Please HELP!
Odin
08-07-2005, 11:59 PM
$tr_num = $_REQUEST['tr_num'];Be careful when you use variables like this in SQL queries, they can be vulnerable to SQL injection attacks (to find out more google it). If your server has magic quotes on it should be fine, but it's always good practice to check variables first, and you won't need to update your code if your server changes configuration or you change hosts. To deal with this I've checked for 0-9 characters only (I'm assuming $tr_num will be intergers only. You could also use is_numeric() instead but I prefer this).
Also $_REQUEST may not be necessary, $_GET might be more suitable unless you are also expecting $tr_num from post forms or cookies.



$result = mysql_query("select * from pack_stat WHERE tr_num=$tr_num order by updated");I can't see your table, but I'm not sure an 'updated' column is required. You are using it to order your results by, but you haven't tried to [incorrectly] output it using the PHP date() function, so I'm guessing it's a plain interger so you can order rows with the same $tr_num. If that's right, wouldn't it be better to just sort by the date and time?
Something like:

$result = mysql_query("select * from pack_stat WHERE tr_num='$tr_num' order by date DESC, time DESC");You could also just use a DATETIME column, unless you have a reason for wanting them separate.



echo "<table BORDER='1' CELLSPACING='1' CELLPADDING='0' WIDTH='585' bordercolordark='#333333' bordercolorlight='#666666'>";
I won't bother fully validating your HTML seen as other people can do a better job, but, as far as I'm aware, you should use double-quotes to specify values in HTML (I also don't think those border attributes are standards). When you want to echo HTML, you might want to use single quotes, or end and restart PHP tags putting your code unprocessed inbetween, so that it remains intact and you don't have to unescape characters.



$tr_num=$r["tr_num"];
...
echo "<tr>".$tr_num=$r["tr_num"]."</td>It's not necessary to reassign the variables when you're only outputting them. When you concatenate your string you should use the variable name only, although to be fair you might've slipped up here by trying to cut time by copy/pasting.



date("m/d/Y",$date=$r["date"])
Now every time the page loads ALL dates are 12/31/1969, and the time is always 16:00.Again, only use a variable in the function. The dates and times are probably like that because PHP does not recognise the timestamp (a date or time won't work with this function), and takes it as 0, and so the beginning of the Unix Epoch. The date and time is 8 hours earlier than this though, so I would guess you live in America and your server is on the west coast and so has a -8 offset from GMT/UTC.
To modify the date and time, you would have to pass the date/time through strtotime() first:
date('d/m/Y', strtotime($r['date']));Or you could use the MySQL functions date_format and time_format to format the date and time whilst you extract them from the database.


Full code:

//Connect to database
mysql_connect ( $dbhost, $dbuser, $dbpass)or die("Could not connect: ".mysql_error());
mysql_select_db($dbname) or die(mysql_error());

$tr_num = $_GET['tr_num'];

if(empty($tr_num) || preg_match('|[^0-9]|', $tr_num)){
echo "Invalid tracking number.";
} else {
$result = mysql_query("select *,date_format(date, '%m/%d/%Y') AS format_date,time_format(time, '%H:%i') AS format_time from pack_stat WHERE tr_num='$tr_num' order by date DESC, time DESC") or die(mysql_error());
if(mysql_num_rows($result) < 1){
echo "No record for this tracking number.";
} else {
?>
<html>
<body>
<center>
<table border="1" cellspacing="1" cellpadding="0" width="585">
<thead>
<td width="70">Tracking#</td>
<td width="50">User</td>
<td width="170">Status</td>
<td width="95">Date</td>
<td width="50">Time</td>
<td width="150">Last Updated</td>
</thead>
<?
while($r = mysql_fetch_array($result)){
echo "<tr><td>".$r['tr_num']."</td>
<td>".$r['user']."</td>
<td>".$r['status']."</td>
<td>".$r['format_date']."</td>
<td>".$r['format_time']."</td>
<td>".$r['updated']."</td></tr>";
}
}
}