Order By FIELD To Sort MySQL Result For ‘IN’ Clause

I got a problem when I tried to sort a MySQL query while I use ‘IN’ clause. For example:

  1. <?php
  2. ...
  3. $ID = 7,4,1,2;
  4. $query = "SELECT ID,Description FROM mytable WHERE ID IN (".$ID.")";
  5. $result = mysql_query($query);
  6. ...
  7. ?>

The result will be displayed with the ID sorted from the less value to the biggest one :

ID    |    Description    |
———————
1    |    Apple        |
2    |    Blueberry    |
4    |    Grape        |
7    |    Strawberry    |

That is not the result we want. I want to display the result sorted based on the ‘IN’ values.

I got a solution for this to add a FIELD command so the result can be sorted based on the ‘IN’ values. the FIELD command can sort numeric and string values as well. So here is the fixed.

  1. <?php
  2. ...
  3. $ID = 7,4,1,2;
  4. $query = "SELECT ID,Description FROM mytable WHERE ID IN (".$ID.") ORDER BY FIELD(ID,".$ID.")";
  5. $result = mysql_query($query);
  6. ...
  7. ?>

And here is the result.

ID    |    Description    |
———————
7    |    Strawberry    |
4    |    Grape        |
1    |    Apple        |
2    |    Blueberry    |