Ch15 - Auction Site - Convert_Tz Returns Null On Live Host

[DaveCoast]

Help - My logged in formatted dates (closed or submitted) are NULL in my sql result set. This only happens on my shared host site (local XAMPP works fine). FYI - I did execute my SQL request with the CONVERT_TZ via phpMyAdmin and that is where I see my column value = NULL.

 

Is there some kind of time zone feature I need to have my hosting provider enable or is this a setting I should be able to set myself or is it time to find a new provider?

 

I'm currently testing the PHP only (non JS) version. All seems to be working fine on my local dev box (XAMPP on Win7). Just for fun - I ftp'd the site up to an Addon Domain on my shared host - MyTestSite with user01/user01pass or user02/user02pass.

 

Here's the SQL setup:

// Set the time zone if visitor is logged in
$localDateClosed = setItDateClosedTimeZone();
$sql = "
SELECT itId, itItem, COALESCE(MAX(btBid), itOpeningPrice) as sqlPrice,
   IF($localDateClosed < DATE_ADD(UTC_TIMESTAMP(), INTERVAL 24 HOUR),
   DATE_FORMAT($localDateClosed, '%l:%i %p'),
   DATE_FORMAT($localDateClosed, '%M %D @ %l:%i %p')) as sqlClose
FROM ch15itemtable
   LEFT JOIN ch15bidtable on itId = btItemId
WHERE itDateClosed > UTC_TIMESTAMP()
GROUP BY itID
ORDER BY itDateClosed ASC";

 

Here's the timezone setup code:

<?php
function setItDateClosedTimeZone() {
if (isset($_SESSION['utTimeZone'])) {
 $tz_column =
	 "CONVERT_TZ(itDateClosed, 'UTC', '{$_SESSION['utTimeZone']}')";
} else {
 $tz_column = 'itDateClosed';
}
return $tz_column;
}
function setBtDateSubmittedTimeZone() {
if (isset($_SESSION['utTimeZone'])) {
 $tz_column =
	 "CONVERT_TZ(btDateSubmitted, 'UTC', '{$_SESSION['utTimeZone']}')";
} else {
 $tz_column = 'btDateSubmitted';
}
return $tz_column;
}
?>

 

 

 

 

Thanks - I appreciate the knowledge gleaned from this forum, Dave

[Larry]

Yes, your hunch is correct (and I'm pretty sure I say this in the book): time zone support must be enabled in MySQL on the server.

[DaveCoast]

Finally got it resolved with my hosting provider - 10 hours and painfully trying to get them to understand the issue.

 

The solution is granting SELECT access to table mysql.time_zone for all of my MySQL accounts.

 

Now it's time to progressively enhance my auction site with the spiffy stuff.

[Larry]

Glad it's working and thanks for sharing your solution!