[SOLVED] [RESOLVED] date formats

[BarneyJoe]

OK - I looked at this a while ago (Nov 23). I have a mySQL database with a date entered field, and am just trying to display it more sensibly in the web pages.

I have a query that looks like :

[code]mysql_select_db($database_Photolibrary, $Photolibrary);
$query_DateFormat = "SELECT date_format(Date_Added, '%D %b %Y') as formatted_date FROM photos";
$DateFormat = mysql_query($query_DateFormat, $Photolibrary) or die(mysql_error());
$row_DateFormat = mysql_fetch_assoc($DateFormat);
$totalRows_DateFormat = mysql_num_rows($DateFormat);[/code]

And I have my output code that looks like :

[code=php:0]<?php echo $row_DateFormat['formatted_date']; ?>[/code]

Basically the day I did this I thought it was working fine, as records I added sure enough displayed as 23rd November 2006.

The trouble is that every record added since then [b][i]also[/i][/b] displays as 23rd November 2006, even tho' the actual date field in the database table has the correct date stored.

Any ideas what's going on here?

[trq]

What field type is Date_Added? And how exactly are you creating these dates when adding a new record?

[ober]

It doesn't look like your grabbing the date from any specific photo.  Are you sure you're not missing a WHERE clause specifying a photo to grab the date for?

[trq]

Doh!

[craygo]

looks like you are grabbing all your records but you are not looping through them so it will only return the last record it has in the array.
[code]<?php
mysql_select_db($database_Photolibrary, $Photolibrary);
$query_DateFormat = "SELECT date_format(Date_Added, '%D %b %Y') as formatted_date FROM photos";
$DateFormat = mysql_query($query_DateFormat, $Photolibrary) or die(mysql_error());
$totalRows_DateFormat = mysql_num_rows($DateFormat);
while($row_DateFormat = mysql_fetch_assoc($DateFormat)){
echo $row_DateFormat['formatted_date']."<br>";
}
?>[/code]

As ober said, if you only want one record add a WHERE clause in there then the loop will not be needed

Ray

[BarneyJoe]

D'oh! I had another query on the page already, but just forgot the WHERE clause in this one.

So just needed to change it to

[code]
("SELECT date_format(Date_Added, '%%D %%b %%Y') as formatted_date FROM photos WHERE Photo_ID = %s", $colname_DateFormat);[/code]

Thank you!