CODEWITHSUNDEEP
phpmysql
Skilldrick
Skilldrick

Reputation: 70839

How can I get a list of ordered rows from a list of ordered ids from a MySQL table?

I'm working in PHP, and I have a list of ids, which are ranked, with the first being the most important. I'm retrieving the rows using something like this (simplified for clarity):

$id_as_sql_list = implode("','", $id_list);

$sql = "SELECT * FROM books
WHERE id in ('$id_as_sql_list')";

This basically works out as:

WHERE id in ('456', '123', '789')

The problem is, the result from the database isn't coming back in the order of the list. What's the best way to get them back in this order?

Upvotes: 3

Views: 96

Answers (3)

Nicola Cossu
Nicola Cossu

Reputation: 56357

Take a look at order by field syntax.

This is an example

select * from table
where id in (x,y,z)
order by field(id,x,y,z)

Upvotes: 4

Mild Fuzz
Mild Fuzz

Reputation: 30691

use ORDER BY

$sql = "SELECT * FROM books
WHERE id in ('$id_as_sql_list') ORDER BY whatever";

where whatever is the field you wish to order by.

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

$sql = "SELECT * FROM books
WHERE id in ('$id_as_sql_list')
ORDER BY YourRankingField ";

Upvotes: 0

Related Questions