Help! With MySQL CASE problem

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

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

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 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
>

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

Re: Re: Help! With MySQL CASE problem

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