用户定义的使用MySQL和PHP的排序 [英] User defined ordering using MySQL and PHP

问题描述

这更多是方法论问题.说我有一张桌子

This is more of a methodological question. Say I have a table

id int auto_increment primary key,
name text not null

我有许多条目想要以某种任意方式进行排序.我可以创建一个界面，该界面允许我更改名称在页面上显示的顺序.当我读出表中的条目时，将根据我的选择对它们进行排序.我看到了三种可能的方法.第一种方法是添加一个字段

I have a number of entries which I want to order in some arbitrary way. I could create an interface that allows me to change the order of the names as they would appear on some page. When I read out the entries of the table, they would be ordered according to how I chose. I see three possible approaches. First approach is to add a field

order int not null

，当我更改顺序时，我必须更新每一行，或者至少更新每一行的顺序要高于我要更改的最低顺序.这似乎是错误的方法，因为它将需要在for循环中执行SQL语句.第二种方法是创建另一个由id链接的表

and when I changed the the order, I would have to update every row, or at least every row with ordering higher than the the lowest order I am changing. This seems like the wrong approach, as it would require doing SQL statements in a for loop. The second approach would be to create another table, linked by id

id int not null
order int not null

但是我在这里会遇到同样的问题.如果我添加了一个名称并想将其放在首位，则必须更改每行中的订单条目.我可以看到第三种可能的方法，即将ID和顺序之间的某些关联存储在单个列中，甚至存储在平面文件中.我可以看到使用JSON格式执行此操作.

but I would run into the same problem here. If I added a name and wanted to put them first, I would have to change the order entry in every row. I could see a possible third approach, which is to store some associations between id and order in a single column, or even in a flat file. I could see using JSON formatting to do this.

我的问题是这个.使用MySQL和PHP做到这一点的最佳方法是什么.

My question is this. What is the best way to do this using MySQL and PHP.

推荐答案

是的，sorting列可以正常工作.为此，您不需要for循环.

Yes a sorting column works fine. You don't need a for loop for this.

插入时，请先增加其他排序值.

When doing an insert first increment the other sorting values.

UPDATE foo
  SET sorting = sorting + 1
  WHERE sorting >= :sorting;

INSERT INTO foo
  SET name = :name, sorting = :sorting;

看到它的工作

在更新时，将特定记录的sorting列设置为新索引，并递增/递减其他记录的sorting以形成有效序列.

See it work

On updating set the sorting column for the specific record to the new index and increment/decrement the sorting for the other records to make a valid sequence.

SELECT @old_sorting:=sorting FROM foo WHERE id = :id;
UPDATE foo
  SET sorting = IF(id = :id, :sorting, sorting + IF(@old_sorting > :sorting, 1, -1))
  WHERE sorting BETWEEN LEAST(@old_sorting, :sorting) AND GREATEST(@old_sorting, :sorting);

看到它的工作

:id，:name和:sorting的值应由您的mysql lib插入

See it work

Values of :id, :name and :sorting should be inserted by your mysql lib

这篇关于用户定义的使用MySQL和PHP的排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！