Formatting Date Output

[Sharif]

Here is the code I used to display the date of an article in between the <title></title> tags:
Only On XBOX | <?php
$display_date = mysql_query("SELECT date FROM news WHERE id=$id")
or die ("MySQL Error");
if (mysql_num_rows($display_date) > 0)
{
while ($row = mysql_fetch_array($display_date))
{
$news_date = $row["date"];
echo ("News: $news_date");
};

}  
else 
{
	echo("OOX ERROR: Bad Link");
}

?>

Now the date for example, comes out like this: 2003-04-27, how do I get it to display as: 04/27/2003. I know there is a way, just don't know how to mix it with the SQL code.

[nnichols]

SELECT DATE_FORMAT(date,'%m/%d/%Y') FROM news WHERE id=$id

Hope this helps 😉

[Sharif]

Hey,

So it should be like this?:

<?php
$display_date = mysql_query("SELECT date_FORMAT(date,'%m/%d/%Y') FROM news WHERE id=$id")
...
?>

[nnichols]

That should do it. You might want to alias the calculated column so you can access it by name -

<?php
$display_date = mysql_query("SELECT DATE_FORMAT(date,'%m/%d/%Y') AS date FROM news WHERE id=$id")
...
?>

[Sharif]

Hey,

I tried it and it only worked when I used the "AS" statement. Can you please explain the AS statement. And also, please explain why used " date,'%m/%d/%Y' " instead of " date() " and the " % " signs. Thanks for all your help.

[nnichols]

The AS allows you to assign a name to the calculated column so that you can access it by name, otherwise the column name would be - "DATE_FORMAT(date,'%m/%d/%Y')" - which is not as easy to type as "date".

You could also access the result using numeric indices -
$row[0] instead of $row['date'] which enables you to use mysql_fetch_row instead of mysql_fetch_array.

If you always access the returned arrays as associative arrays (using the key names) you should use mysql_fetch_assoc instead of mysql_fetch_array as it is minutely more efficient.

Hope this helps 😉

P.S. You should read through the section of the php manual relating to mysql functions as it describes the difference between the functions and their uses.

[Sharif]

Originally posted by nnichols
If you always access the returned arrays as associative arrays (using the key names) you should use mysql_fetch_assoc instead of mysql_fetch_array as it is minutely more efficient.

Would my code change a lot if I used this? Would it execute quicker? Would the code be like this?:

<?php
$display_date = mysql_query("SELECT date FROM news WHERE id=$id")
or die ("MySQL Error");
if (mysql_num_rows($display_date) > 0)
{
while ($row = mysql_fetch_assoc($display_date))
{
$news_date = $row["date"];
echo ("News: $news_date");
};

}
else
{
echo("OOX ERROR: Bad Link");
}
?>

[nnichols]

The speed difference will be negligible. The pros are more to do with memory resources when a site is under heavy load.

In this case it will make little/no difference as the data being returned is very small in quantity, but it is something to consider when creating pages that take a lot of data from your db.

If in doubt - experiment 😉

[Sharif]

Been a while since I posted this but I need help again,

I've been using DATE_FORMAT(date,'%m/%d/%Y') AS date to format how the date is shown when I display it using echo(). I want to customize it more, I want it to show up like this: Month/Day/Year At Hour:Minute:Second AM/PM. How can I do this, I tried using %g and the other time stuff in the manual but they don't work right.

Instead of using DATE_FORMAT(date,'%m/%d/%Y'), is it possible to use DATE_FORMAT(date())?