SiteCrafting, Inc.

One of the common needs for a content management system (hey, that's what we make!) is some form of page ordering. Clients need to be able to manually order pages to suit their fancy, rather than relying on something arbitrary like update time or alphabetical order. For this we use a simple numeric field. On any given given page in the admin center, the user can shuffle around all that page's siblings, which are then posted and have their ordernum fields reset. Typically this is done with a query apiece, as one would expect when trying to update multiple rows with multiple different values on multiple keys. Wouldn't it be nice, though, if there were an easy way to perform this same entire action with a single query?

We'll start with the simplest of simple tables: an auto-increment ID and an ordernum field, currently set to match the ID:

mysql> SELECT * FROM pages;
+----+----------+
| id | ordernum |
+----+----------+
|  1 |        1 | 
|  2 |        2 | 
|  3 |        3 | 
|  4 |        4 | 
|  5 |        5 | 
+----+----------+
5 rows in set (0.00 sec)

Now, say the user submits an update, choosing to order the pages as follows: 5, 2, 3, 1, 4 (the page with id=5 first, id=2 second, etc.) We want to achieve the same result as this series of statements:

mysql> UPDATE pages SET ordernum = 4 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE pages SET ordernum = 2 WHERE id = 2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> UPDATE pages SET ordernum = 3 WHERE id = 3;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> UPDATE pages SET ordernum = 5 WHERE id = 4;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE pages SET ordernum = 1 WHERE id = 5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

The key to this is use of MySQL's FIELD() function. FIELD accomplishes a fairly basic task: finding a value's location in a list, where the first term is the needle, and the remaining terms the haystack. For example, FIELD("a", "b", "c", "a") will return 3, because the search term, "a", appears third on the following list.

Using this function, we still loop through all the newly submitted ordernum values, but rather than running an UPDATE each time, we build a quick comma-separated list: 5,2,3,4,1. Or better still, just use the implode function in php (assuming these values are coming through as an array). We can then use this list as the haystack, and the id field as the needle, like so:

mysql> SELECT id, FIELD(id,5,2,3,1,4)
    -> FROM pages;
+----+---------------------+
| id | FIELD(id,5,2,3,1,4) |
+----+---------------------+
|  1 |                   4 | 
|  2 |                   2 | 
|  3 |                   3 | 
|  4 |                   5 | 
|  5 |                   1 | 
+----+---------------------+
5 rows in set (0.00 sec)

This shows us a direct correlation between each row in the table and the new ordernum, because FIELD is returning each id's location in the newly submitted order. This result can easily be translated into an UPDATE statement instead of a SELECT:

mysql> UPDATE pages  
    -> SET ordernum = FIELD(id,5,2,3,1,4);
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 3  Warnings: 0

mysql> SELECT * FROM pages;
+----+----------+
| id | ordernum |
+----+----------+
|  1 |        4 | 
|  2 |        2 | 
|  3 |        3 | 
|  4 |        5 | 
|  5 |        1 | 
+----+----------+
5 rows in set (0.00 sec)

And voila! All the ordering updated in a single shot.

So what if you only want to update a small set of rows (say, only one branch of a page tree)? Well, if your tree is based on a parent_id field in each row, you can add that in a WHERE clause:

mysql> UPDATE pages  
    -> SET ordernum = FIELD(id,5,2,3,1,4);
    -> WHERE parent_id = 9
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 3  Warnings: 0

If you don't have such a field, you can use the very same comma-separated listed from your FIELD function, like so:

mysql> UPDATE pages
    -> SET ordernum = FIELD(id,5,2,3,1,4)
    -> WHERE id IN(5,2,3,1,4);
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 3  Warnings: 0

In addition to the mass update, this function can be used for on-the-fly output ordering by shifting it to the ORDER BY clause:

mysql> SELECT id    
    -> FROM pages
    -> ORDER BY FIELD(id,5,2,3,1,4);
+----+
| id |
+----+
|  5 | 
|  2 | 
|  3 | 
|  1 | 
|  4 | 
+----+
5 rows in set (0.00 sec)

And don't forget that this ORDER BY trick is true of any function that will return a numeric value for each row in the result set.