It seems to me that " '%m/%e/%Y' " could be the culprit.

m = month
e should be d = day
y = year

I take it that eventdate is a CHAR or VARCHAR field in your database? Try:
ORDER BY date_format(eventdate, '%m/%e/%Y')

Actually it is a DATETIME field. I've tried the order by date_format and it doesn't affect it.

It is definitely the date_format in the select statement that is causing the problem. When I take it out, it orders fine. But when I put it in, the year seems to be ignored in the "ORDER BY" part. How weird that it affects the "ORDER BY" part.

Ooops- ignore what I said. Try:
SELECT *, date_format(eventdate, '%m/%e/%Y') as eventdate1
FROM ".TBL_EVENTS."
WHERE active = 'TRUE'
ORDER BY eventdate

That way you're sorting by the actual DATETIME field, not the Month/Day/Year character string.

Thanks. I suspected I had to do that. Now I will have to change my code everywhere to use eventdate1 and hope I don't break something!

I just had a sudden inspiration- if you haven't started updating your code, try one more thing:

SELECT *, date_format(eventdate, '%m/%e/%Y') as eventdate1
FROM ".TBL_EVENTS."
WHERE active = 'TRUE'
ORDER BY TBL_EVENTS.eventdate

This *may* force the ordering by using the actual data in eventdate instead of the aliased eventdate. I haven't tied it to know for sure, but it seems like it should work...

I'm having the same problem: it worked last year but tis year, my "order by datetime_field" isn't working.

Here is my query string:

$result = mysql_query( "SELECT * FROM schedule WHERE status != 'Completed' && status != 'Cancelled' ORDER BY 'when' ASC" )

The output is in the order inputted into the database instead of by datetime field.

All help appreciated

What type of field is the 'when' field and are you sure the data is being entered correctly? (Please provide some sample data to show what it is doing and how it should be ordered.)

added the following:
Actually, it looks like the single quotes around when is your problem. Try the field name without the single quotes.

[edited by: LifeinAsia at 4:29 pm (utc) on Sep. 5, 2008]

Here is my script:
<table align="center">
<?PHP
include("includes/dbconnect.php");

$result = mysql_query
("
SELECT *
FROM schedule
WHERE (status != 'Completed' && status != 'Cancelled')
ORDER BY 'when' ASC
")
or die("SELECT Error: ".mysql_error());

$num_rows = mysql_num_rows($result);
echo "<tr><td><b>$num_rows visits listed</b></td></tr>";
while($row = mysql_fetch_array( $result )) {
$time=strtotime($row[when]);
echo "<tr><td valign='top'><b>";
echo date('l, M jS Y \a\t g:i A',$time);
echo "</b></td></tr>";
}
echo "</table>";

include("includes/dbclose.php");
?>

And I get this output:

9 visits listed
Saturday, Dec 20th 2008 at 7:00 PM
Tuesday, Dec 9th 2008 at 12:00 PM
Saturday, Dec 6th 2008 at 9:30 AM
Thursday, Dec 18th 2008 at 10:00 AM
Thursday, Dec 18th 2008 at 2:00 PM
Saturday, Nov 29th 2008 at 3:00 PM
Saturday, Dec 20th 2008 at 5:00 PM
Friday, Dec 12th 2008 at 2:00 PM
Saturday, Nov 15th 2008 at 9:00 AM

Then I removed the single quote around 'when' and I get this result:

"SELECT Error: 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 'when ASC' at line 1"

OK, so it looks like 'when' is actually a VARCHAR field instead of a DATETIME field. So your query is sorting by the character values, not the converted time. Echo the when field and post the results.

Nope. I just checked to make sure, "when" is a "datetime" field and the data within the field is in the following format (actually, this is some of the data):

2008-12-20 19:00:00
2008-12-09 12:00:00
2008-12-06 09:30:00
2008-12-18 10:00:00
2008-12-18 14:00:00
2008-11-29 15:00:00
2008-12-20 17:00:00
2008-12-12 14:00:00
2008-11-15 09:00:00

Here's the modified script:

<table border=1 align="center">
<?PHP
include("includes/dbconnect.php");

$result = mysql_query
("
SELECT *
FROM schedule
WHERE (status != 'Completed' && status != 'Cancelled')
ORDER BY 'when' ASC
")
or die("SELECT Error: ".mysql_error());

$num_rows = mysql_num_rows($result);
echo "<tr><td><b>$num_rows visits listed</b></td></tr>";
echo "<tr><th>datetime field</th><th>desired output</th></tr>";
while($row = mysql_fetch_array( $result )) {
$time=strtotime($row[when]);
echo "<tr><td>".$row['when']."</td><td valign='top'><b>";
echo date('l, M jS Y \a\t g:i A',$time);
echo "</b></td></tr>";
}
echo "</table>";

include("includes/dbclose.php");
?>

And this is the output:

9 visits listed
datetime field desired output
2008-12-20 19:00:00Saturday, Dec 20th 2008 at 7:00 PM
2008-12-09 12:00:00Tuesday, Dec 9th 2008 at 12:00 PM
2008-12-06 09:30:00Saturday, Dec 6th 2008 at 9:30 AM
2008-12-18 10:00:00Thursday, Dec 18th 2008 at 10:00 AM
2008-12-18 14:00:00Thursday, Dec 18th 2008 at 2:00 PM
2008-11-29 15:00:00Saturday, Nov 29th 2008 at 3:00 PM
2008-12-20 17:00:00Saturday, Dec 20th 2008 at 5:00 PM
2008-12-12 14:00:00Friday, Dec 12th 2008 at 2:00 PM
2008-11-15 09:00:00Saturday, Nov 15th 2008 at 9:00 AM

9 visits listed

(the following is actually a table with two data cells per line:)

datetime field desired output
2008-12-20 19:00:00Saturday, Dec 20th 2008 at 7:00 PM
2008-12-09 12:00:00Tuesday, Dec 9th 2008 at 12:00 PM
2008-12-06 09:30:00Saturday, Dec 6th 2008 at 9:30 AM
2008-12-18 10:00:00Thursday, Dec 18th 2008 at 10:00 AM
2008-12-18 14:00:00Thursday, Dec 18th 2008 at 2:00 PM
2008-11-29 15:00:00Saturday, Nov 29th 2008 at 3:00 PM
2008-12-20 17:00:00Saturday, Dec 20th 2008 at 5:00 PM
2008-12-12 14:00:00Friday, Dec 12th 2008 at 2:00 PM
2008-11-15 09:00:00Saturday, Nov 15th 2008 at 9:00 AM

If it's actually a DATETIME field, why are you using strtotime on it? strtotime converts a string to a datetime.

Other than that, I agree- it doesn't make much sense.

[edited by: LifeinAsia at 6:22 pm (utc) on Sep. 5, 2008]

I don't know -- I assumed that once the results were returned that the output was programaticly treated as a string. When I originally tried to change the format of the date in my report, I tried "echo date('l, M jS Y \a\t g:i A',$row[when]);" but it didn't wor until I converted the string with "strtotime".

Here is the output using the above line in the script (eliminating "strtotime"):

9 visits listed
datetime field desired output
2008-12-20 19:00:00 Wednesday, Dec 31st 1969 at 4:33 PM
2008-12-09 12:00:00 Wednesday, Dec 31st 1969 at 4:33 PM
2008-12-06 09:30:00 Wednesday, Dec 31st 1969 at 4:33 PM
2008-12-18 10:00:00 Wednesday, Dec 31st 1969 at 4:33 PM
2008-12-18 14:00:00 Wednesday, Dec 31st 1969 at 4:33 PM
2008-11-29 15:00:00 Wednesday, Dec 31st 1969 at 4:33 PM
2008-12-20 17:00:00 Wednesday, Dec 31st 1969 at 4:33 PM
2008-12-12 14:00:00 Wednesday, Dec 31st 1969 at 4:33 PM
2008-11-15 09:00:00 Wednesday, Dec 31st 1969 at 4:33 PM

Just for fun, try (or something like that- I don't know PHP):
$time=strtotime($row[when]);
echo "<tr><td>".$row['when']."</td><td>";
echo $time;
echo "</td>";

Here's the result:

9 visits listed
2008-12-20 19:00:00 1229828400
2008-12-09 12:00:00 1228852800
2008-12-06 09:30:00 1228584600
2008-12-18 10:00:00 1229623200
2008-12-18 14:00:00 1229637600
2008-11-29 15:00:00 1227999600
2008-12-20 17:00:00 1229821200
2008-12-12 14:00:00 1229119200
2008-11-15 09:00:00 1226768400

Bizarre. No more ideas here. Sorry. :(

Thanx for trying, though. 8)

Hey, I know this is a little late but try this

$result = mysql_query( "SELECT * FROM schedule WHERE status != 'Completed' && status != 'Cancelled' ORDER BY [when] ASC" )

the IMPORTANT part being [when] rather than 'when'

Hope that helps :-)

EDIT: Sorry probably should have explained why!
when is a reserved word in SQL so that is why you receive an error with "ORDER BY when ASC" as this makes no sense to SQL

if you have "ORDER BY 'when' ASC", SQL is looking for a reference to the object 'when' which actually doesn't exist - as 'when' has not been assigned to anything.

by using [] brackets around reserved words SQL knows that you wish to use this as an object rather than as a reserved word

Best practice is to name you fields, tables etc with prefixes eg(tbl_schedule) or (fld_when) OR use names that are not reserved words in SQL

Cheers

[edited by: Alt_F4 at 4:52 am (utc) on Oct. 16, 2008]