[SOLVED] Time Format!!

[iceman023]

I can't believe its taking me this long to figure out how to go from

 

2009-05-04 00:00:00

 

to 05/04/09

 

I have all the dates that a photo was upload to the database in a field called date. I just want to change the display of the format from the database.

Some please help!

 

Pic of the database attached

 

[attachment deleted by admin]

[Ken2k7]

SELECT DATE_FORMAT(`Date`, '%m/%d/%y') AS `date` FROM tablename

[iceman023]

Thank you but where do i put this.. Within the same SELECT statment i already have or do i make a new one. and if so, does it need a while loop...

[Ken2k7]

In your SQL.

[iceman023]

IS this what you ment or no...

$results = mysql_query("
SELECT * FROM Photos 
WHERE Published = 'no' 
AND DATE_FORMAT(`Date`, '%m/%d/%y') AS `Date` FROM Photos
ORDER BY ID DESC LIMIT $page, $limit");
while ($data = mysql_fetch_array($results))
{

[Ken2k7]

No. Put it in the SELECT clause. I also assume the column name is Date?

[iceman023]

Isn't that the select clause??? and yes it is

[iceman023]

Or is it this one

 

$numresults = mysql_query("
SELECT * FROM Photos 
WHERE Published = 'no' 
AND DATE_FORMAT(`Date`, '%m/%d/%y') AS `Date` FROM Photos
ORDER BY ID DESC"); // the query.
$numrows = mysql_num_rows($numresults); // Number of rows returned from above query.
if ($numrows == 0){
echo("No results found matching your query - $query"); // bah, modify the "Not Found" error for your needs.
exit();}

[Ken2k7]

--__-- no...

 

SELECT DATE_FORMAT(...) AS `date`, * ... 

[iceman023]

Sorry kinda new to this..

 

$numresults = mysql_query("
SELECT DATE_FORMAT(`Date`, '%m/%d/%y') AS `Date`,
* FROM Photos	
WHERE Published = 'no' 
ORDER BY ID DESC"); // the query.
$numrows = mysql_num_rows($numresults); // Number of rows returned from above query.
if ($numrows == 0){
echo("No results found matching your query - $query"); 
exit();}

[Ken2k7]

Looks fine to me.

[iceman023]

Doesnt work tho... 

[Ken2k7]

Is there a SQL error? Can you post the error?

[iceman023]

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in....

 

 

 

 

in php MyAdmin sql it say....

 

 

Error

SQL query: 

 

SELECT DATE_FORMAT( `Date` , '%m/%d/%y' ) AS `Date`

FROM Photos, *

FROM Photos

WHERE Published = 'no'

ORDER BY ID DESC

LIMIT 0 , 30

 

MySQL said: 

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM Photos   

WHERE Published = 'no'

ORDER BY ID DESC

LIMIT 0, 30' at line 2

[iceman023]

Sorry this one...

 

 

Error

SQL query: 

 

SELECT DATE_FORMAT( `Date` , '%m/%d/%y' ) AS `Date` ,

* FROM Photos

WHERE Published = 'no'

ORDER BY ID DESC

LIMIT 0 , 30

 

MySQL said: 

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM Photos   

WHERE Published = 'no'

ORDER BY ID DESC

LIMIT 0, 30' at line 2

[Ken2k7]

Oh, you put the asterick first.

 

So like - SELECT *, DATE_FORMAT...

[iceman023]

THANK YOU! 

 

PERFECT

[iceman023]

THANK YOU 

 

PERFECT

[iceman023]

I hate to be a pain in the ass but

 

I have this

 

SELECT * , DATE_FORMAT( `Date` , '%m %d %y' ) AS `Date` 
FROM Photos
WHERE Published = 'no'
ORDER BY ID DESC 
LIMIT 0 , 30 

 

and the output is "2009-05-04"

shouldnt it be.... "05.04.09"

 

IF not how do i get "05.04.09"

 

[iceman023]

NEVERMIND i had to change the second one also.. Thanks again!