MySQL ORDER BY IN()

Question

Hi all,

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

You'll just need to give the correct order by statement.

SELECT ID FROM myTable WHERE ID IN(1,2,3,4) ORDER BY ID

Why would you want to get your data ordered unordered like in your example?

If you don't mind concatening long queries, try that way:

SELECT ID FROM myTable WHERE ID=1
UNION
SELECT ID FROM myTable WHERE ID=3
UNION
SELECT ID FROM myTable WHERE ID=2

Answer 2

Standard SQL does not provide a way to do this (MySQL may, but I prefer solutions that are vendor-neutral so I can switch DBMS' at any time).

This is something you should do in post-processing after the result set is returned. SQL can only return them in an order specified in the "order by" clause (or in any order if there's no such clause).

The other possibility (though I don't like it, I'm honor-bound to give you the choice) is to make multiple trips to the database, one for each ID, and process them as they come in:

select * from tbl where article_id = 4;
// Process those.
select * from tbl where article_id = 7;
// Process those.
: : : : :
select * from tbl where article_id = 9;
// Process those.

Answer 3

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

Answer 4

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.

Answer 5

Since you know the IDs and the order and each ID corresponds to one row, you could loop through the IDs and query the DB for each ID separately. I know it's more popular to try get all your data in one query, but it would be more simple and straight forward to do a loop.

$id_array = array(4,7,3,8,9);

foreach ($id_array as $id) {
    $result_row = mysqli -> query("SELECT * FROM sometable WHERE ID = '$id');
    // Now output it to the user or add it to some larger array
}