Hello. I'm trying to display a MySQL timestamp in the user's timezone for each entry in a databse table. BTW, this is OsCommerce, so disregard the weird function names.
This code converts the timestamp for one entry....
PHP Code:
$timestamp_from_mysql = tep_db_query("SELECT UNIX_TIMESTAMP(`date_added`) FROM `".TABLE_TUTORING_UNITS_COMMENTS."` WHERE `customers_id` = '".$customer_id."' ORDER BY `date_added`");
$timezone_from_mysql = tep_db_query("SELECT `customers_timezone` FROM `".TABLE_CUSTOMERS."` WHERE `customers_id` = '".$customer_id."'");
if($timestamp_from_mysql!=false&&$timezone_from_mysql!=false){
$timestamp_from_mysql=tep_db_fetch_array($timestamp_from_mysql);
$timezone_from_mysql=tep_db_fetch_array($timezone_from_mysql);
$timestamp=$timestamp_from_mysql['UNIX_TIMESTAMP(`date_added`)'];
$timezone=$timezone_from_mysql['customers_timezone'];
$dt_zone=new DateTimeZone($timezone);
$time=date('r',$timestamp);
$dtime=new DateTime($time);
$dtime->setTimeZone($dt_zone);
echo $dtime->format("M. j, Y \a\\t g:i a");
}
This is my current code...
PHP Code:
$tutoring_comments_query = tep_db_query("select comments from " . TABLE_TUTORING_UNITS_COMMENTS . " where customers_id = '" . (int)$customer_id . "' order by date_added");
if (tep_db_num_rows($tutoring_comments_query)) {
while ($tutoring_comments = tep_db_fetch_array($tutoring_comments_query)) {
$timezone_from_mysql = tep_db_query("SELECT `customers_timezone` FROM `".TABLE_CUSTOMERS."` WHERE `customers_id` = '".$customer_id."'");
$timestamp_from_mysql = tep_db_query("SELECT UNIX_TIMESTAMP(`date_added`) FROM `".TABLE_TUTORING_UNITS_COMMENTS."` WHERE `customers_id` = '".$customer_id."' ORDER BY `date_added`");
if($timestamp_from_mysql!=false&&$timezone_from_mysql!=false){
$timestamp_from_mysql=tep_db_fetch_array($timestamp_from_mysql);
$timezone_from_mysql=tep_db_fetch_array($timezone_from_mysql);
$timestamp=$timestamp_from_mysql['UNIX_TIMESTAMP(`date_added`)'];
$timezone=$timezone_from_mysql['customers_timezone'];
$dt_zone=new DateTimeZone($timezone);
$time=date('r',$timestamp);
$dtime=new DateTime($time);
$dtime->setTimeZone($dt_zone);
}
echo ' <tr>' . "\n" .
' <td class="main" align="center">' . ' ' . $dtime->format("M. j, Y \a\\t g:i a") . ' ' . '</td>' . "\n" .
' <td class="main" align="center">' . ' ';
if ($orders_history['customer_notified'] == '1') {
echo tep_image(DIR_WS_ICONS . 'tick.gif', ICON_TICK) . ' ' . "</td>\n";
} else {
echo tep_image(DIR_WS_ICONS . 'cross.gif', ICON_CROSS) . ' ' . "</td>\n";
}
echo ' <td class="main">' . ' ' . nl2br(tep_db_output($tutoring_comments['comments'])) . ' </td>' . "\n" .
' </tr>' . "\n";
}
} else {
echo ' <tr>' . "\n" .
' <td class="main" colspan="5"> There are no tutoring comments at this time.</td>' . ' ' . "\n" .
' </tr>' . "\n";
}
Currently, I'm getting several entries so I know the main while loop is working. It's just that the timestamps that come from that are all displaying the time of the first entry instead of the time for each entry. So, it seems like the second while loop (the one for converting the timezone) is only getting run once.
I'm not married to the nested while structure. If someone has a better idea, please enlighten me.
Thanks!