Ordering By Date (A Trip To PHP-Earth)
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());
?>
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'];
?>
You can see both of these at onlinelba.com/loyola.html
Thanks for the help.
Suzanne posted this at 00:19 — 9th April 2004.
She has: 5,507 posts
Joined: Feb 2000
ORDER BY field [ASC | DESC]
TIME
kb posted this at 00:50 — 9th April 2004.
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 posted this at 01:05 — 9th April 2004.
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.
Make sure there are no spaces in the TIME fields?
Suzanne posted this at 01:07 — 9th April 2004.
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)
kb posted this at 03:11 — 9th April 2004.
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 posted this at 03:54 — 9th April 2004.
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.
kb posted this at 14:37 — 9th April 2004.
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
Suzanne posted this at 19:19 — 9th April 2004.
She has: 5,507 posts
Joined: Feb 2000
I'm never answering questions late at night anymore, lol... Sorry!
I don't think you can use DATE_FORMAT on just TIME.
kb posted this at 19:33 — 9th April 2004.
He has: 1,380 posts
Joined: Feb 2002
ok...i'll try it with TIME_FORMAT and see how it goes
Suzanne posted this at 20:16 — 9th April 2004.
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?
kb posted this at 20:34 — 9th April 2004.
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.