使用“排序”列在MySQL表中排列行

Question

I arrange photos on an album by using a column "sort" in the table. This means that an album will be displayed with "ORDER BY sort".

I have a feature that allows users to arrange the photos on an album, by dragging and dropping photos, in javascript, and then pressing the "Save" button.

An array (with the sorted Photo IDs) is sent to the process (in PHP), and then I want to re-sort the rows on the table.

The easiest way in my mind to do is:

for ($c=0; $c<$length; $c++) {
    mysql_query('UPDATE photos SET sort="'.$c.'" WHERE id="'.$array[$c].'"');
}

(please ignore sanitization, duplicates and other verifications here)

But I'm worried about the amount of queries that are made in a cycle like this.

How would you improve this approach?

Thank you.

Answer 1

First of all, I don't think there's anything wrong with issuing multiple queries. Did you test it? Are you sure it would be a performance bottleneck? I don't think so.

But anyway, solution #1 is to use REPLACE in conjunction with the VALUES clause. In that case, though, you need to explicitly specify data for all columns of the table, otherwise the data in those columns will be erased (set to default value).

Solution #2 is just for fun. I don't think anyone would use it, but still:

UPDATE photos
SET sort = (
  SELECT sort FROM (
    SELECT 1 id, 1 sort UNION
    SELECT 2 id, 2 sort UNION
    SELECT 123 id, 3 sort) t
  WHERE t.id = photos.id)
WHERE id IN (1, 2, 123)

Solution #3 is virtually the same as Zombaya's:

UPDATE photos
SET sort =
  IF(id = 1, 1,
  IF(id = 2, 2,
  IF(id = 123, 3,
    0)))
WHERE id IN (1, 2, 123)