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.

$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 [man]strtotime/man with the column value, and use that output of strtotime as the second paramter of [man]date/man.

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

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

dan90joe

dagon;10933274 wrote:
i prefer to do this kind of thing via mysql DATE_FORMAT function
$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:

$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']