surfmad74 Posted April 20, 2012 Share Posted April 20, 2012 Hi I've created the Message Board site as per the book but for some reason the Date & Time on both the Forum.php and read.php page aren't displaying. I've tried the supplied code as well as my own and neither work so I'm assuming it might be a php setting or MySQL setting? I've run the site using XAMPP on my MacOSX and also on my rented server. I don't know if it makes any difference but the php.ini file timezone is set to... date.timezone = "Europe/London" Can you help please, many thanks Grant Link to comment Share on other sites More sharing options...
HartleySan Posted April 20, 2012 Share Posted April 20, 2012 I'd make a separate sample script to just test some date functions and make sure they're working all right. Try the following in XAMPP and confirm whether date info is properly displayed or not: <?php echo date('r') . '<br>'; print_r(getdate()); ?> Please let us know what you find. Thanks. Link to comment Share on other sites More sharing options...
surfmad74 Posted April 20, 2012 Author Share Posted April 20, 2012 HartleySan.... that worked! I received Fri, 20 Apr 2012 13:43:06 +0100Array ( [seconds] => 6 [minutes] => 43 [hours] => 13 [mday] => 20 [wday] => 5 [mon] => 4 [year] => 2012 [yday] => 110 [weekday] => Friday [month] => April [0] => 1334925786 ) So what can II try next? thanks for your help!!! Link to comment Share on other sites More sharing options...
HartleySan Posted April 20, 2012 Share Posted April 20, 2012 Could you please post what you suspect to be the relevant (troublemaking) code? Thanks. Link to comment Share on other sites More sharing options...
surfmad74 Posted April 20, 2012 Author Share Posted April 20, 2012 In the database the date is stored like so... posted_on 2012-04-18 18:29:15 Below is the code from the page 'forum.php' <?php # Script 15.4 - forum.php // This page shows the threads in a forum. include ('includes/header.html'); // Retrieve all the messages in this forum... // If the user is logged in and has chosen a time zone, // use that to convert the dates and times: if (isset($_SESSION['user_tz'])) { $first = "CONVERT_TZ(p.posted_on, 'UTC', '{$_SESSION['user_tz']}')"; $last = "CONVERT_TZ(p.posted_on, 'UTC', '{$_SESSION['user_tz']}')"; } else { $first = 'p.posted_on'; $last = 'p.posted_on'; } // The query for retrieving all the threads in this forum, along with the original user, // when the thread was first posted, when it was last replied to, and how many replies it's had: $q = "SELECT t.thread_id, t.subject, username, COUNT(post_id) - 1 AS responses, MAX(DATE_FORMAT($last, '%e-%b-%y %l:%i %p')) AS last, MIN(DATE_FORMAT($first, '%e-%b-%y %l:%i %p')) AS first FROM threads AS t INNER JOIN posts AS p USING (thread_id) INNER JOIN users AS u ON t.user_id = u.user_id WHERE t.lang_id = {$_SESSION['lid']} GROUP BY (p.thread_id) ORDER BY last DESC"; $r = mysqli_query($dbc, $q); if (mysqli_num_rows($r) > 0) { // Create a table: echo '<table width="100%" border="0" cellspacing="2" cellpadding="2" align="center"> <tr> <td align="left" width="50%"><em>' . $words['subject'] . '</em>:</td> <td align="left" width="20%"><em>' . $words['posted_by'] . '</em>:</td> <td align="center" width="10%"><em>' . $words['posted_on'] . '</em>:</td> <td align="center" width="10%"><em>' . $words['replies'] . '</em>:</td> <td align="center" width="10%"><em>' . $words['latest_reply'] . '</em>:</td> </tr>'; // Fetch each thread: while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) { echo '<tr> <td align="left"><a href="read.php?tid=' . $row['thread_id'] . '">' . $row['subject'] . '</a></td> <td align="left">' . $row['username'] . '</td> <td align="center">' . $row['first'] . '</td> <td align="center">' . $row['responses'] . '</td> <td align="center">' . $row['last'] . '</td> </tr>'; } echo '</table>'; // Complete the table. } else { echo '<p>There are currently no messages in this forum.</p>'; } // Include the HTML footer file: include ('includes/footer.html'); ?> Link to comment Share on other sites More sharing options...
HartleySan Posted April 20, 2012 Share Posted April 20, 2012 I'm not that familiar with the message board code in the book (sorry), but am I to assume that all the posts, etc. display properly aside from the dates and times not displaying? If that's the case, then most likely there is some mismatch between the date and time stored in the DB and what you're requesting, or there's some syntax error with the DATE_FORMAT function in the DB query. Either way, I would recommend going into phpMyAdmin in XAMPP and from the "SQL" tab of the DB page, and trying to execute simple queries using the DATE_FORMAT function and static values. See if you can't diagnose the problem like that. After you do that, please let us know what you find. I'm honestly still not 100% sure what the exact problem is, so it's hard to help. Link to comment Share on other sites More sharing options...
Edward Posted April 20, 2012 Share Posted April 20, 2012 In order to use the function CONVERT_TZ(), the list of time zones must already be stored in MySQL, which may or may not be the case for your installation. So if the times zones are not installed the function will return a NULL value, that's why may be your are not receiving any output. 1 Link to comment Share on other sites More sharing options...
surfmad74 Posted April 20, 2012 Author Share Posted April 20, 2012 So if I add to the php.ini file the other timezones that should work? Like so? date.timezone = "Europe/London" date.timezone = "America/New_York" etc Link to comment Share on other sites More sharing options...
Larry Posted April 20, 2012 Share Posted April 20, 2012 No, you need to install the list of timezones in MySQL. See Chapter 14 (I think) of the book. Link to comment Share on other sites More sharing options...
sandari Posted August 28, 2014 Share Posted August 28, 2014 If you go to: http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html you will find full instructions on loading time zones into MySQL Link to comment Share on other sites More sharing options...
Recommended Posts