[thelist] PHP/MySQL Case Statement Problem

Phil Cooper Phil.Cooper at zirconsoftware.co.uk
Mon Nov 8 05:19:47 CST 2004

I was wondering if anyone could shed some light on a problem I have encountered as I educate myself in the ways of php/mysql.  

I have been creating some php pages to query a MySQL database using a CASE statement in the query to allow me to create a custom order in which the data is returned - code is as follows:

$query = "SELECT *, CASE pos WHEN 'QB' THEN 1 WHEN 'RB' THEN 2 WHEN 'WR' THEN 3 WHEN 'TE' THEN 4 WHEN 'PK' THEN 5 WHEN 'LB' THEN 6 WHEN 'DL' THEN 7 WHEN 'DT' THEN 8 WHEN 'DE' THEN 9 WHEN 'CB' THEN 10 WHEN 'DB' THEN 11 WHEN 'S' THEN 12 ELSE NULL END AS orderMe FROM t_roster_$id LEFT JOIN t_players ON t_players.player_id=t_roster_$id.player_id LEFT JOIN t_rookies ON t_rookies.player_id=t_roster_$id.player_id Order By orderMe;";

On my local test server this works fine as the the information is displayed nicely ordered as I want it. However, when I upload to my webhost then the information is displayed unordered (well in the order that the data was added to the table) as though the case statement was being completely ignored.  I was wondering if this was down to the versions of PHP/MySqL.  My webhost uses PHP v4.3.8 and MySQL v4.0.20.  Locally I am using PHP v4.3.6 and MySQL 4.0.21.  

If this is not it has anyone got any clues as to why it isn't working. After googling for a day I haven't come up with anything - I expect it is something really simple but I just can't see it.



More information about the thelist mailing list