MySQL ORDER BY IN()

Question

I have a PHP array with numbers of ID's in it. These numbers are already ordered.

Now i would like to get my result via the IN() method, to get all of the ID's.

However, these ID's should be ordered like in the IN method.

For example:

IN(4,7,3,8,9)   

Should give a result like:

4 - Article 4   7 - Article 7   3 - Article 3   8 - Article 8   9 - Article 9 

Any suggestions? Maybe there is a function to do this?

Thanks!

Answer 1

I think you may be looking for function FIELD -- while normally thought of as a string function, it works fine for numbers, too!

ORDER BY FIELD(field_name, 3,2,5,7,8,1) 

Answer 2

You could use FIELD():

ORDER BY FIELD(id, 3,2,5,7,8,1) 

Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found.

It's kind of an ugly hack though, so really only use it if you have no other choice. Sorting the output in your app may be better.