PHP Date formatting - MySQL

[SteveB_NI]

I'd appreciate any help with this problem as although I've been trawling tutorials online etc I still can't get my head around the date formatting concept when displaying data from MySQL in PHP.

Code is as follows:

$events = @mysql_query('SELECT StartDate, EndDate, EventTitle FROM events WHERE StartDate >= NOW()');

while ($event = mysql_fetch_array($events))
{
$startdate = $event['StartDate'];

echo $startdate;

}


Now when I read some tutorials it suggested putting in DATE_FORMAT before the StartDate field in the MySQL select query, but this is not working for me when using the following code:

$events = @mysql_query('SELECT DATE_FORMAT(StartDate, '%d'), EndDate, EventTitle FROM events WHERE StartDate >= NOW()');

It is not displaying any results now. I changed the types of quotation marks around the SQL query incase that made a different but to no joy.


Any assistance would be appreciated! Basically I just want it to display 20th instead of 2008-06-20 etc.

Thanks

 

[TwoSaints]

I think rather than DATE_FORMAT is is after the format you wish the date to be displayed, this can be done by using the following page as a reference PHP Date

so a format of: l, jS F Y
would show a date like: Saturday, 14th June 2008

 

[SteveB_NI]

Thanks for your message Jonny_No2 - I have looked at that manual but still cannot make any sense out of it.

Is there any chance you could give me a brief example of code to explain?

Thanks very much

 

[Sic]

echo date('l, jS F Y',strtotime($event['StartDate']));

 

[TwoSaints]

I am not that hot on coding in mySQL but based on what you have said above the code below should hopefully give the output you are looking for, i.e. 20th

$events = @mysql_query('SELECT jS(StartDate, '%d'), EndDate, EventTitle FROM events WHERE StartDate >= NOW()');

this is because PHP will reference the j to the day of the month number

j Day of the month without leading zeros 1 to 31

and S will put in the 'th' or whatever else is relevant

S English ordinal suffix for the day of the month, 2 characters st, nd, rd or th. Works well with j

Sorry if this doesn't help much.

 

[SteveB_NI]

Thats great thanks guys - decided to go with the PHP formatting of the date using strtotime.

Thanks again

Edit: Any ideas how I can do the reverse? i.e when putting a value (e.g. 24th August 2008) into a MySQL database format (2008-08-24?)

 

[Beansprout]

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format

$events = @mysql_query('SELECT DATE_FORMAT(StartDate, '%d') AS StartDate, EndDate, EventTitle FROM events WHERE StartDate >= NOW()');

 

[Beansprout]

Edit: Any ideas how I can do the reverse? i.e when putting a value (e.g. 24th August 2008) into a MySQL database format (2008-08-24?)

http://dev.mysql.com/doc/refman/5.0/en/datetime.html

INSERT INTO table VALUES ('2008-08-24')

 

[furnace]

Any ideas how I can do the reverse? i.e when putting a value (e.g. 24th August 2008) into a MySQL database format (2008-08-24?)

You'd probably need to write a script to convert "24th August 2008" into "2008-08-24". Why do you need to do that, though? I should expect there's a nicer way to do it that doesn't involve 'unformatting' a date.

 

[Beansprout]

You'd probably need to write a script to convert "24th August 2008" into "2008-08-24". Why do you need to do that, though? I should expect there's a nicer way to do it that doesn't involve 'unformatting' a date.

Oops, misread. MySQL's str_to_date() function will do all this for you

It's amazing how many people must not have read the MySQL manual and will do absolutely absurd things to work with dates. Even popular commercial billing scripts!