Topic: mySQL IN statement and order

$arr=array(5,7,2,3,1);
$implode=implode(",",$arr);
        
$result=$db->query('SELECT myinfo FROM mytable WHERE id IN(".$implode.")");

while($sub=$db->fetch_assoc($result)){
//spit out the information
}

Using the IN syntax in mysql I'm able to extract data based on an array. The problem is that the database is ordering the information from the database as 1,2,3,5,7 rather than the order it appears originally. What could I do to force mysql to order the information in the same order as the array?

On a side note, why is IN() rarely mentioned in any mysql documentation? sad

~~Creaturecorp

I don't HAVE a signature, ok?

Re: mySQL IN statement and order

Boing

I don't HAVE a signature, ok?

Re: mySQL IN statement and order

i doubt its possible,

4

Re: mySQL IN statement and order

Yes, it is possible.

$arr=array(5,7,2,3,1);
$implode=implode(",",$arr);
$order = implode(", id=", $arr);
        
$result=$db->query('SELECT myinfo FROM mytable WHERE id IN(".$implode.") order by id=" . $order);

while($sub=$db->fetch_assoc($result)){
//spit out the information
}

But does it look nice?
Nope.
wink

The German PunBB Site:
PunBB-forum.de

Re: mySQL IN statement and order

Tobi, you're brilliant!!! Fabulous solution. Will it cause any load increase?

I don't HAVE a signature, ok?

6

Re: mySQL IN statement and order

Depends on your table.
An order on a heavy table always causes load especially when the field you order by is not indexed.
If you have a normally frequented board I wouldn't say it's an issue.

The German PunBB Site:
PunBB-forum.de

Re: mySQL IN statement and order

i didn't realise you could do orders like that smile

8

Re: mySQL IN statement and order

Yes, you can, and it is especially useful when you have fields with flags (like status=1, status=2 etc) where the numbers don't reflect the order that you want them to appear.

The German PunBB Site:
PunBB-forum.de