formatting MySQL datetime on SELECT

jonnyboy

Please can someone help!

I have been scanning various forums and cannot find the definitive answer to my problem. I am new to php so some of the posts may be accurate but I need more information.

I have created a bullitin board using PHP and MySQL. I am writing a value of "NULL" to a "datetime" MySQL field called "message_stamp".

On a standard select query, I can retrieve the date and time in the following format:

2002-02-02 16:00:01

However, I would like to present the date as:

Sunday, 2nd February 2002 @ 16:00

Can someone please be so kind as to explain what code I need to use to manipulate the output of the date and where I need to insert the various snippets of code.

Many thanks

Jonathan

Sphynx

Hi there!
Simply grab your date record and use strtotime() to get it into a timestamp format. Then just use the date() function to format your string.

<?php
$mydate = strtotime("2002-02-02 16:00:01");
echo date("D, F d",$mydate)." @ ".date("G:i:s",$mydate);
?>

Take a look at the manual for more info 🙂
http://www.phpbuilder.com/manual/function.date.php

jonnyboy

Thanks very much!

I can see what this does, however I need to make this work in my array.

Here is the snippet of the array:

//Print the message creator
echo "\n<tr>\n\t<td bgcolor=\"silver\">" .
"" .
"Posted by " .
$row["message_creator"] .
" on: " .
$row["message_stamp"] .
"\n";

"message_stamp" being the MySQL date I have to convert.

Thanks

Jonathan

Sphynx

//Print the message creator 
echo "\n<tr>\n\t<td bgcolor=\"silver\">" . 
"<font color=\"black\">" . 
"Posted by <b>" . 
$row["message_creator"] . 
"</b> on: <b>" . 
date("D, F d",$row["message_stamp"])." @ ".date("G:i:s",$row["message_stamp])."</font></b>\n";

umm... yeah... I think that looks right 🙂

jonnyboy

I got a parse error first, then I added " before the last closing square bracket. This outputs the following, which is close in format (not sure what the trailing 'n' is), however it does not seem to be seeing / converting the MySQL record...

Thu, January 01 @ 0:33:23n

Any ideas?

Thanks

Jonathan

xblue

the n probably was meant to be \n (line break in html source code), the forum tends to eat backslashes sometimes.

anyway, I'd always recommend to do the formatting right within the mysql query, like this:

SELECT DATE_FORMAT(column_name, '%W, %D %M @ %T') AS date FROM table_name

for available formatting options, see
http://www.mysql.com/doc/en/Date_and_time_functions.html

jonnyboy

Brilliant! That worked.

Thanks alot.

Jonathan🙂

Sphynx

ah, sweet. thanks xblue!