How to get a unique and ordered list of id's from a database should be clear by most of my readers. It is surely also possible to sort the result in the database direclty and get it as an array, for example:
SELECT id FROM entry WHERE user_id=5 ORDER BY last_change DESC;
->
[321, 320, 318, 315, 300]
The example above is really easy. We order the entrys of user 5 by it's last change to see the newest or last changed element in the first slot. But what, if we want to let the user set the order of the entries by itself? We would add a new column for that purpose, so that we could run the following query:
SELECT id FROM entry WHERE user_id=5 ORDER BY sort_col DESC;
->
[315, 321, 318, 320, 300]
This is really good, because the only thing we have to do on INSERT operations is building a ascending list in sort_col using MAX(sort_col) + 1 to get the highest free possible index. The problematic part is, the reordering of that list. What if we want to allow the user to change the order by a super-duper high end GUI? What if the user exchanged two elements of our array, so that we have the following array content:
[315, 318, 321, 320, 300]
How could we write this order to the database? The cleanest way would be writing all updates in an array and update successive to the database like the changes were made by the user. In the example above this would be easy because we only have to update the second and third element but under a more general view this is not a good idea. The next step in mind could be, easily loop over the array and write every index of the array to the database. This is better, because we are not bound to the number of changes but to the number of elements. Also an implementation like shuffle([315, 318, 321, 320, 300]) is no problem any more. A naive implementation could look like this:
<?php
for($i=0, $n=count($arr); $i < $n; $i++) {
$db->query('UPDATE entry SET sort_col='.($n - $i + 1).' WHERE user_id=5 AND id='.$arr[$i]);
}
?>
I don't like that; it's also lame. After a few minutes of brood over the problem, I got a nice idea to break down from O(n) to O(1) - at least on the client side. All the magic is done by the MySQL function FIELD(). To implement the example of above a little cleaner we could write it steady as:
<?php
$db->query('UPDATE entry SET sort_col=FIELD(id, '.implode(',', $arr).') WHERE user_id=5');
?>
BUT! you should only use this example, if you really can trust the data inside of the array. If not, loop mysqli_real_escape_string() over the array using array_map(). As I said earlier, this is a good example of using JavaScript to change the order and pass the result as CSV string to the server. But as it comes from the client, we have to validate the input either by using the earlier mentoined array_map() cleanup-method or a little faster way using strical() from my PHP Infusion extension. Sure you could also use preg_match() to validate the input string. Expect, that the input is "315,318,321,320,300," we use this code to write the new order of the id's to the database:
<?php
if(strcal('0-9,', $_GET['data'])) {
$db->query('UPDATE entry SET sort_col=FIELD(id, '.trim($_GET['data'], ',').') WHERE user_id=5');
// success
} else {
// attack detected
}
?>