CODEWITHSUNDEEP
mysql
Polar Bear
Polar Bear

Reputation: 131

How to retrieve items in a manually-specified order

I have a list of id's in PHP. For example:

$id = (4,6,3,2,5,1)

When I perform a query like this:

$query = mysql_query("SELECT * FROM table WHERE ID IN ($id)");

it retrieves the results I want but the items are returned in the insertion order.

Instead, I want to retrieve the results in the order given in my array:

information 4, information 6, information 3, information 2, information 5, information 1

How can I do that?

Thanks guys! solution is ORDER BY FIELD (ID, $id);

Upvotes: 0

Views: 54

Answers (4)

Hanky Panky
Hanky Panky

Reputation: 46910

$query="SELECT * FROM table WHERE ID IN ($id) ORDER BY FIND_IN_SET(ID,$id) DESC";

Also check this

Select query using IN() and without any sorting

Upvotes: 1

alwaysLearn
alwaysLearn

Reputation: 6950

Try this

$query = "SELECT * FROM table WHERE ID IN ($id) ORDER BY FIELD(ID,$id) ";

you can see this http://marco-pivetta.com/mysql-custom-sorting-rule-mysql/

Upvotes: 3

John Woo
John Woo

Reputation: 263733

You can use FIELD on this,

SELECT * 
FROM table 
WHERE ID IN (4,6,3,2,5,1)
ORDER BY FIELD(ID, 4,6,3,2,5,1)

Upvotes: 1

Explosion Pills
Explosion Pills

Reputation: 191749

$query = "SELECT * FROM table WHERE ID IN ($id) ORDER BY ";

foreach ($id as $i) {
   $query .= "ID = $i,";
}
$query = mysql_query(rtrim($query, ','));

Your code is vulnerable to injection. You should use properly parameterized queries with PDO or mysqli.

Upvotes: 1

Related Questions