根据用户偏好在表格内排序项目，正确的方法是什么？

After seeing joomla's way of having arrows to order items (articles, menu entries and the likes), I was wondering what is considered best practice to shift elements inside a table.

在看到joomla使用箭头订购商品(文章,菜单条目等)的方式之后,我想知道在表格中移动元素被认为是最佳做法。

You obviously must have a field inside a table that hold numbers, letters, or anything you may want to use to define an order, then you have to change this order, I can't think on any elegant way to achieve this, can you help me?

你显然必须在一个表格中有一个字段,里面有数字,字母或任何你想用来定义订单的东西,然后你必须改变这个顺序,我想不出任何优雅的方式来实现这个,你能不能帮我?

Thank you very much

非常感谢你

EDIT: Reading the answer I realize that i may not have been clear, I don't need to order items based on standard parameters (like I don't know, date, id, etc). I need to decide an order myself. Like this:

编辑:阅读答案我意识到我可能不清楚,我不需要根据标准参数(如我不知道,日期,身份等)订购项目。我需要自己决定订单。像这样:

id - name - order
1    one    2
2    two    1
3    three  3

I want to know the correct way to change the order of the items (like if I want to have one go into the first position I would need to change the order value of two to '2' and the value of one to '1'.

我想知道更改项目顺序的正确方法(例如,如果我想让一个进入第一个位置,我需要将2的订单值更改为'2',将值1更改为'1' 。

Hope I explained myself correctly

希望我能正确解释自己

5 个解决方案

#1

Use big numbers for ordering:

使用大数字进行订购:

id - name - order
1    one    200000
2    two    100000
3    three  300000

When you need to move 3 between 2 and 1 just change its order value in the middle between 100000 and 200000: 150000.

当您需要在2和1之间移动3时,只需在100000和200000之间的中间更改其订单值:150000。

Eventualy you'll need to insert element between two adjacent numbers. If that happens just rewrite order column for all rows first, using 100000 step again. Choose your step carefully, that you'll not overflow your type, and that you'll not have to rewrite the whole table too often.

因此,您需要在两个相邻数字之间插入元素。如果发生这种情况,则首先重写所有行的订单列,再次使用100000步骤。仔细选择你的步骤,你不会溢出你的类型,并且你不必经常重写整个表。

#2

if you have little table you can do it in client side using jquery plug-in ,

如果你有小桌子,你可以使用jquery插件在客户端进行,

link to demo : http://tablesorter.com/docs/

链接到演示:http://tablesorter.com/docs/

what you need is save for every user in another table the preference of field sort , like by name after by age ....

您需要的是为另一个表中的每个用户保存字段排序的首选项,例如按年龄后的名称....

after that only what you need to do is in js section set this field in the

之后只有你需要做的是在js部分设置这个字段

$(document).ready(function()     {         $("#myTable").tablesorter( {sortList: [[0,0], [1,0]]} );     } );

in this solution you do not need to change the query

在此解决方案中,您无需更改查询

#3

The easiest way is to have an "order by" clause in the statement that is used to generate the table, and have this controlled by a link sigil in each header cell, like the arrow in Joomla.

最简单的方法是在用于生成表的语句中使用“order by”子句,并使其在每个标题单元格中由链接符号控制,如Joomla中的箭头。

...so when someone clicks on the arrow (or whatever you use), it's a hyperlink back to the page, only with an "orderby" CGI variable, like `http://mydomain.com/mypage.php?orderby=salestotal'.

...所以当有人点击箭头(或你使用的任何东西)时,它是一个回到页面的超链接,只有一个“orderby”CGI变量,比如`http://mydomain.com/mypage.php?orderby= salestotal”。

Then you modify your query to use that variable:

然后修改查询以使用该变量:

$sql = 'select col1, col2, col3 from table where col2 = somevalue';
if ($_REQUEST['orderby']) $sql .= " order by ' . $_REQUEST['orderby'];

You'll need to fiddle with quoting etc. if it's a string value, and if the table was generated with CGI variables in the first place, you'll have to cater for managing that state too.

如果它是一个字符串值,你需要摆弄引号等,如果表格首先是用CGI变量生成的,那么你也必须满足于管理那个状态。

It'd be better (but significantly harder!) to handle the table's regeneration within the page using Ajax or jquery or something (as suggested by a different answer), in order to avoid reloading the whole page with its state.

使用Ajax或jquery或其他东西(由不同的答案建议)来处理页面在页面内的重生会更好(但更难!),以避免重新加载整个页面的状态。

#4

Looing at the EDIT that you have put in, I understand what you are trying. I have implemented a solution in a similar way as you have mentioned, using and Order column. And then using the Order column in the ORDER BY clause.

在你编辑的EDIT中,我明白你在尝试什么。我已经使用和Order列以类似的方式实现了解决方案。然后使用ORDER BY子句中的Order列。

One best practise tip would be to use numbers like 10, 20, 30 etc instead of 1, 2 ,3. This gave me the flexibility to insert a new Order value row between say, 10 and 20, like 15, for something new that came along at a later point in time.

一个最佳实践提示是使用数字,如10,20,30等,而不是1,2,3。这使我可以灵活地在一个新的Order值行之间插入10和20之间的值,如15,用于稍后出现的新内容。

#5

Keep you data as a linked list:

将数据保存为链接列表:

id parent name
-- ------ -----

1  2      one
2  0      two
3  1      three

and query like this:

并查询如下:

SELECT  @r AS _parent,
        @r := (
        SELECT  id
        FROM    mytable
        WHERE   parent = _parent
        ) AS id
FROM    (
        SELECT  @r := 0
        ) vars,
        t_list

This will return you the list in correct order:

这将以正确的顺序返回列表:

id parent name
-- ------ -----

2  0      two
1  2      one
3  1      three

Moving an item or even a block of items in such a design takes an update of but three rows at most.

在这样的设计中移动项目甚至是一块项目最多只更新三行。

See this entry in my blog for a stored procedure to move items over the list:

请参阅我的博客中的此条目,了解在列表中移动项目的存储过程:

• Sorting lists: moving items

排序列表:移动项目