ORDER BY help

[ptmuldoon]

I'm trying have a query where I can set the list a specific order of a players games.

I know the below is wrong, I'd like to something like the following:

$order = "inactive, trading, attacking, waiting";
sql = "SELECT player, state FROM game WHERE player = user ORDER BY {$order} ";

[guelphdad]

two methods one proprietary to mysql, one standard sql


ORDER BY FIELD(fieldname,'inactive', 'trading', 'attacking', 'waiting')

substituting in the name of your field/column for fieldname of course.

and standard sql solution:


ORDER BY
CASE
WHEN fieldname = 'inactive' THEN 1
WHEN fieldname = 'trading' THEN 2
WHEN fieldname = 'attacking' THEN 3
WHEN fieldname = 'waiting' THEN 4
END