Ordering items inside a table based on user preferences, what is the correct way?

Question

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.

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'.

Hope I explained myself correctly

Answer 1

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

link to demo : 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

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

in this solution you do not need to change the query

Answer 2

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.

...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'.

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.

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.

Answer 3

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.

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.

Answer 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.

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.

Answer 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