CODEWITHSUNDEEP
phpmysql
Talon
Talon

Reputation: 4995

Sorting items in a MySQL database based on the order of another column in PHP

I have a database that looks something like this

Title     |     ID
---------------------------------
Hello           45
Hi              23
Yo              52
Test            76

And then I have another table that looks like this:

List                 |   Order
---------------------------------
Main List                76,23,45,52

What I want to do is query the database and output the title of the first table based on the order of the 2nd table.

Any ideas on the best way to do something like this?

Upvotes: 1

Views: 65

Answers (1)

Colin M
Colin M

Reputation: 13348

SELECT
   *
FROM
    menu m
    INNER JOIN links l ON (l.menu_id = m.menu_id)
WHERE
    m.menu_id = 123
ORDER BY
    FIND_IN_SET(l.link_id, m.menu_order)

Obviously you'll need to change to reflect your actual table and column names

Per Michael Berkowski in the comments below, if you don't have a link between the two tables (so you can't join), you can accomplish the same with just:

SELECT t1.*
FROM t1
ORDER BY FIND_IN_SET(ID, (SELECT `Order` FROM t2))

Feel free to mess around with his SQLFiddle example: http://sqlfiddle.com/#!2/947e3/2

Thanks, Michael

Upvotes: 3

Related Questions