Steve Steve - 3 years ago 133
MySQL Question

Completely arbitrary sort order in MySQL with PHP

I have a table in MySQL that I'm accessing from PHP. For example, let's have a table named THINGS:

things.ID - int primary key - varchar

things.owner_ID - int for joining with another table

My select statement to get what I need might look like:

SELECT * FROM things WHERE owner_ID = 99;

Pretty straightforward. Now, I'd like users to be able to specify a completely arbitrary order for the items returned from this query. The list will be displayed, they can then click an "up" or "down" button next to a row and have it moved up or down the list, or possibly a drag-and-drop operation to move it to anywhere else. I'd like this order to be saved in the database (same or other table). The custom order would be unique for the set of rows for each owner_ID.

I've searched for ways to provide this ordering without luck. I've thought of a few ways to implement this, but help me fill in the final option:

  1. Add an INT column and set it's value to whatever I need to get rows
    returned in my order. This presents the problem of scanning
    row-by-row to find the insertion point, and possibly needing to
    update the preceding/following rows sort column.

  2. Having a "next" and "previous" column, implementing a linked list.
    Once I find my place, I'll just have to update max 2 rows to insert
    the row. But this requires scanning for the location from row #1.

  3. Some SQL/relational DB trick I'm unaware of...

I'm looking for an answer to #3 because it may be out there, who knows. Plus, I'd like to offload as much as I can on the database.

Answer Source

From what I've read you need a new table containing the ordering of each user, say it's called *user_orderings*.

This table should contain the user ID, the position of the thing and the ID of the thing. The (user_id, thing_id) should be the PK. This way you need to update this table every time but you can get the things for a user in the order he/she wants using ORDER BY on the user_orderings table and joining it with the things table. It should work.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download