Sorting database depending on different string values

[Sharkadder]

Hi there,I have just finished creating a display form for somebody which gets values from a mysql database. The problem is that the person i have created the display form for wants the database to be sorted depending on certain criteria, i shall explain.The database stores information about football/soccer players, when i get data from the database in php i wish for the data to be sorted depending on the players position in which they play. I could just order a column by their position but some players have more than one position and so it would be awkward...not sure if a LIKE clause could be used here.Basically in the database there's a column called "positions", i'd like it sorted by GK, then CD, then M and lastly by ST.GK = goal keeper, CD = central defender, M = Midfielder and ST = striker, in the database i use position codes to save space.Some players however have more than one position arn't always stored as just one value e.g. some players might be both "m" and "st" and so their positions would say "M ST". How would i check the database to sort by GK, CD, M and then ST for the positions column and for the players who have more than one position it would either get the first position typed in or by when the search matches that position with something like a LIKE clause.I am doing this in PHP and MYSQL, here is my failed query attempt so far:

$query="SELECT * FROM FootballPlayers WHERE TeamName LIKE '%$squad%' ORDER BY CASE Positions WHEN 'GK' THEN 1 WHEN 'CD' THEN 2 WHEN 'M' THEN 3 WHEN 'ST'";

p.s. squad is a valuable holding information about a specific squad. Do you also think this order by operation should be feasible to do if the database will have over 1000 entries?Thanks,Mark

[justsomeguy]

Put your numbers in a column, and then sort by that column.SELECT field1, field2, CASE Positions WHEN 'GK' THEN 1 WHEN 'CD' THEN 2 WHEN 'M' THEN 3 WHEN 'ST' AS sort_order FROM ... ORDER BY sort_order

[Sharkadder]

wooow i recall you answering some of my problems years ago man, i wasn't as experienced then haha.So how would i do this if the column contained more than one value? e.g. somebody might be both "M and ST" and so they'd be "M ST". will i have to add in a WHEN clause for that?Here is my new code and i've added in some more cases too.

$query="SELECT * CASE Positions WHEN 'GK' THEN 1 WHEN 'LB' THEN 2 WHEN'CD' THEN 2 WHEN 'RB' THEN 3 WHEN 'DM' THEN 4 WHEN 'LW' THEN 5 WHEN 'RW' THEN 6 WHEN 'AM' THEN 7 WHEN 'ST' ELSE 'Positions' END AS sort_order FROM FootballPlayers WHERE TeamName LIKE '%$squad%'";$result=mysql_query($query);$num=mysql_numrows($result);

With the above code i just get the following error below using PHP, this seemed to be fine when i got all rows from the database with my WHERE clause before i used any CASE or WHEN statement. IF i wish to have all rows selected do i have to specify them individually like you showed?

Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in /home/content/S/h/a/Sharkadder/html/worldfootballleagues/sql_testing/squad.php on line 71

Perhaps you can show me where i am going wrong as the query is obviously invalid and i'm not sure the else clause in which i used is correct, i just wish else clause to equal the value of positions.Thanks

[justsomeguy]

Is "ST" missing a value? It looks like it just says "when 'ST'". If you want to use a column value, just write the column name without quotes. MySQL might be complaining that you're trying to mix numbers and strings in the same column, so the numbers might need to be strings. You can have PHP get the error message from MySQL to see what it's saying though:$result=mysql_query($query) or exit(mysql_error());

[Sharkadder]

ok i managed to sort it by using this syntax:

$query="SELECT * FROM FootballPlayers WHERE TeamName LIKE '%$squad%' ORDER BY CASE WHEN Positions LIKE 'GK%' THEN 1 WHEN Positions LIKE 'SW%' THEN 2 WHEN Positions LIKE 'LB%' THEN 3 WHEN Positions LIKE 'CD%' THEN 4 WHEN Positions LIKE 'RB%' THEN 5 WHEN Positions LIKE 'DM%' THEN 6 WHEN Positions LIKE 'LW%' THEN 7 WHEN Positions LIKE 'CM%' THEN 8  WHEN Positions LIKE 'RW%' THEN 9 WHEN Positions LIKE 'AM%' THEN 10 WHEN Positions LIKE 'FW%' THEN 11 WHEN Positions LIKE 'ST%' THEN 12 ELSE 13 END";

It is sorted now, for some reason i couldn't use LIKE clause like i have done above when i specified "Positions" column just after the CASE statement and so i've had to do individual checks. The current code now checks the start of each string in positions column for the names given (hence the % after the name in the LIKE clauses). Now when i run the script even people with more than one position get sorted properly as their main position is displayed first.Many thanks for the help justsomeguy i certainly learnt something new here.