PHP Date

[dan90joe]

This is gonna sound like a complete newbie question, but i have a bunch of fields in a table, one of which is a "date" field.

PHP Code:

$result = mysql_query("SELECT * FROM Guests WHERE Date>=CURDATE() ORDER BY Date LIMIT 6");

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

echo"<table width='720' border='0' align='center'><tr>";
echo"<td><span class='first'><strong>" . $row['Date'] . "</strong></span></td>";


The page is set to show all row's on or after the current date. This works fine at the moment but i would like to change the format of the date on the output page, as it is currently set to the default of 2009-11-15...

I've searched and searched online and tried a whole bunch of different things for about an hour now and cant seem to make it work. Would like the format of the date to be "15th November 2009" which i think is "jS F Y" according to the PHP manual.

Hopefully somebody can shed some light,

Thanks,
Dan

[bpat1434]

Use strtotime() with the column value, and use that output of strtotime as the second paramter of date().

PHP Code:

while ($row = mysql_fetch_array($result))
{
    echo '<td><span class="first"><strong>'.date('jS F Y', strtotime($row['Date'])).'</strong></td>';
}


Hope that helps.

[dan90joe]

Perfect Thanks - i had come across Strtotime in my searches but didnt know how to include it in my presentation layer...

Thanks!

[dagon]

i prefer to do this kind of thing via mysql DATE_FORMAT function

PHP Code:

$result = mysql_query("SELECT Guests.*, DATE_FORMAT(Date,'%D %M %Y') as Date FROM Guests WHERE Date>=CURDATE() ORDER BY Date LIMIT 6");


[dan90joe]

Originally Posted by dagon

i prefer to do this kind of thing via mysql DATE_FORMAT function

PHP Code:

$result = mysql_query("SELECT Guests.*, DATE_FORMAT(Date,'%D %M %Y') as Date FROM Guests WHERE Date>=CURDATE() ORDER BY Date LIMIT 6");


i tried it your way too, but it messed about with the data that was displayed, didnt show this months details, but instead December's. not sure why...

[dagon]

sorry, as your using Date in the where clause rewriting its format is messing with that, simple solution:

PHP Code:

$result = mysql_query("SELECT Guests.*, DATE_FORMAT(Date,'%D %M %Y') as formatted_date FROM Guests WHERE Date>=CURDATE() ORDER BY Date LIMIT 6");

//then output
$row['formatted_date']