Ordering By Date (A Trip To PHP-Earth)

He has: 1,380 posts

Joined: Feb 2002

Nice title, eh? (The Hobbit, anyone?)

Anyyyways, I have a problem. I am pulling some stuff out of a dB, and ordering by date, but the dates are in 2004 AND 2005. The January 2005 stuff is being printed before December 2004. My current query is as follows:

<?php
$sql
= mysql_query(\"SELECT DATE_FORMAT(time, '%l:%i %p') AS time, activity, qual, place,
DATE_FORMAT(date, '%m/%d/%Y') AS date FROM loyola WHERE date >= NOW() ORDER BY date\")
or die(mysql_error());
?>
(without the hard returns)

Also, I am printing times from the same dB, but they either don't show up, or show up as 12:00 AM...i have the field 'time' set as TIME in MySQL, and all of them have values. I set the variable $time like

<?php
    $time
= $qry['time'];
?>
and then call it as $time. Any ideas?

You can see both of these at onlinelba.com/loyola.html

Thanks for the help.

Suzanne's picture

She has: 5,507 posts

Joined: Feb 2000

He has: 1,380 posts

Joined: Feb 2002

Well after reading that...it seems to think that my time values are illegal...but they aren't

Example: 16:00:00 [4 PM]......14:00:00 [2 PM]

All of the times are legal....

About the ASC/DESC...I didn't know MySQL had online doc...thanks.

Suzanne's picture

She has: 5,507 posts

Joined: Feb 2000

you might want to check the data types for that field and do a test on inserting the values, illegal values are probably illegal characters or wrong spacing so the time doesn't make sense.

Quote: Illegal TIME values are converted to '00:00:00'. Note that because '00:00:00' is itself a legal TIME value, there is no way to tell, from a value of '00:00:00' stored in a table, whether the original value was specified as '00:00:00' or whether it was illegal.

Make sure there are no spaces in the TIME fields?

Suzanne's picture

She has: 5,507 posts

Joined: Feb 2000

(sorry, I gave you information you already know on the time, I waited too long to post)

He has: 1,380 posts

Joined: Feb 2002

no spaces, no weird characters, ...thats why I posted. It doesn't make sense.

Heres a dump of the data:

id activity qual place time date
1 Loyola vs. Gilman Diving I Gilman 16:00:00 2004-11-30
2 Loyola vs. LaSalle, Malvern Modified Diving Loyola 12:00:00 2004-12-04
3 Loyola vs. JC, St. P & P Varsity Only, Diving II Loyola 15:30:00 2004-12-07
4 Loyola vs. Curley Varsity Only, Diving IV Loyola 15:30:00 2004-12-14
5 Loyola vs. Severn Diving V Loyola 15:30:00 2005-01-04
6 Loyola vs. McDonough Diving III Loyola 15:30:00 2005-01-11
7 Loyola vs. Mt. St. Joe Diving I Mt. St. Joe 15:30:00 2005-01-25
8 National Catholics Qualifiers Only Villanova Univ. 00:00:00 2005-01-29
9 National Catholics Qualifiers Only Villanova Univ. 00:00:00 2005-01-30
10 Loyola vs. Calvert Hall Diving IV Calvert Hall 15:30:00 2005-02-08
11 MIAA Championships (1) Pre-lims Loyola 09:00:00 2005-02-12
12 MIAA Championships (2) Finals Loyola 18:00:00 2005-02-12
13 MIAA Championships Diving Loyola 12:00:00 2005-02-12
14 Easterns Qualifiers Only LaSalle 00:00:00 2005-02-25
15 Easterns Qualifiers Only LaSalle 00:00:00 2005-02-26

Suzanne's picture

She has: 5,507 posts

Joined: Feb 2000

Seems like midnight and noon are being reported identically, as well.

I did a quick test on my local server and didn't get the same results with your data as you're getting. How are you transforming the time to report AM and PM and such?

Edit to add file of db structure. I didn't pay attention to the other fields, just the ones in question. Smiling

He has: 1,380 posts

Joined: Feb 2002

<?php
DATE_FORMAT
(time, '%l:%i %p') AS time
?>

That's in the original sql query...the online doc says that should transform it between AM and PM

He has: 1,380 posts

Joined: Feb 2002

ok...i'll try it with TIME_FORMAT and see how it goes

Suzanne's picture

She has: 5,507 posts

Joined: Feb 2000

<?php
$sql
= mysql_query(\"SELECT TIME_FORMAT(time, '%l:%i %p') AS time, activity, qual, place, 
DATE_FORMAT(date, '%m/%d/%Y') AS date FROM loyola WHERE date >= NOW() ORDER BY date\")
or die(mysql_error());
?>

That works for me as intended. All these options, eh?

He has: 1,380 posts

Joined: Feb 2002

lol thx

Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.