Re: Help! With MySQL CASE problem

Good people of php-db, I think I have this solved. For those keeping score, repeating the CASE condition in the ORDER BY seems to work, i.e., SELECT DISTINCT e.exhibition_id, e.title, e.begin_date, CASE 'heading' WHEN UNIX_TIMESTAMP( ) >= e.begin_date THEN 'Coming Up' ELSE 'Now Showing' END 'heading', e.end_date, special FROM exhibition e WHERE e.end_date >= UNIX_TIMESTAMP( ) ORDER BY CASE WHEN UNIX_TIMESTAMP( ) >= e.begin_date THEN 'Coming Up' ELSE 'Now Showing' END , e.begin_date ASC I don't know why, however. Thanks for your indulgence, Andrew On 10/15/06, Andrew Darby <adarby@gmail.com> wrote:

Hello, all. I'm having a problem with a php/mysql app which is probably in the SQL, so please don't get angry at me. Basically, I have a CASE statement that works on my localhost, but doesn't seem to get recognised on the production server (i'm running php/mysql 5.x on my localhost, 4.x of both on the production server). Query looks like this: SELECT DISTINCT e.exhibition_id, e.title, e.begin_date, CASE 'heading' WHEN UNIX_TIMESTAMP( ) >= e.begin_date THEN 'Coming Up' ELSE 'Now Showing' END 'heading', e.end_date, special FROM exhibition e WHERE e.end_date >= UNIX_TIMESTAMP() ORDER BY heading DESC , e.begin_date ASC On my localhost, the results look like this: exhibition_id - title - begin_date - heading - end_date - special

84      20/21 Vision    1159599600      Now Showing     1161154800      1
85      David S         1161327600      Coming Up       1162972800      0
86      Yang H         1161327600       Coming Up       1162972800      0

but on the production server looks like this:

85      David S         1161327600      Coming Up       1162972800      0
84      20/21 Vision    1159599600      Now Showing     1161154800      1
86      Yang H         1161327600       Coming Up       1162972800      0

I need it to sort like the localhost, and can't figure out what's happening. I can't seem to ORDER BY at all on the production server. Any ideas? I'm going nuts. Thanks, Andrew