[PHP-DB] Re: Help! With MySQL CASE problem

[Andrew Darby]

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 [EMAIL PROTECTED] 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 Vision1159599600  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 Vision1159599600  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



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Re: Help! With MySQL CASE problem

[Chris]

Andrew Darby wrote:

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.


I think that's an sql standard thing.. because the heading column is 
being made up (by the case statement) you can't use the alias in an 
order by or group by.


--
Postgresql  php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php