How to make MySQL return an pre-ordered list

[wavesurf]

I have an array of id-numbers (3,5,4,2,1) and I want to do a SELECT-query where I select the content based on those ids.

However I need the result set in the order of the array, but when I do a "SELECT * FROM table WHERE id IN 3,5,4,2,1" it gives me the result set ordered with 1,2,3,4,5. I have also tried to include an ORDER BY id IN 3,5,4,2,1 and it still doesn't work, although it gives me a weird order of 2,4,3,5,1.

Does anyone know how to fix this.

Thanks for any help.

[coopster]

Build your ORDER BY clause on-the-fly as well as your IN clause...

$array = array(3,5,4,2,1); 
$list = implode(',', $array); 
$orderbylist = ''; // initialize 
foreach ($array as $id) { 
 $orderbylist .= "ORDER BY id=$id, "; 
} 
$orderbylist = rtrim($orderbylist, ', '); 
$sql = "SELECT * FROM table WHERE id IN ($list) $orderbylist"; 

[wavesurf]

I tried that, and this is the query:

SELECT *
FROM articles
WHERE articleId
IN ( 1305585, 1305588, 1305589, 1305587, 1305576 )
ORDER BY articleId = 1305585,
ORDER BY articleId = 1305588,
ORDER BY articleId = 1305589,
ORDER BY articleId = 1305587,
ORDER BY articleId = 1305576,

However that doesn't work, and gives this error message:

1064 - You have an error in your SQL syntax near 'ORDER BY articleId = 1305588,
ORDER BY articleId = 1305589,
ORDER BY articl' at line 6

Do you know why this is happening and what have I done wrong?

[coopster]

Nothing. Sorry, typo on my part. The ORDER BY should be in the initialization of the variable and the DESC sort sequence keyword needs to be applied. Try this...

$array = array(3,5,4,2,1);  
$list = implode(',', $array);  
$orderbylist = 'ORDER BY '; // initialize  
foreach ($array as $id) {  
 $orderbylist .= "id=$id DESC, ";  
}  
$orderbylist = rtrim($orderbylist, ', ');  
$sql = "SELECT * FROM table WHERE id IN ($list) $orderbylist";