Saving the order of a list (php/zend/jquery)

Question

I'm using the jQuery library to have an ordered list with the ability to re-order items with drag and drop. I want to send the information of the order to the database and the next time you refresh the page (or load it on another browser/computer) it would have your order.

I have a table for each element of the list in mySQL and right now I'm ordering by the creation_date. So what is the best solution and where (and how) do I save the order?

Answer 1

I suppose adding an order field to the table, so you update the information with an AJAX call each time you make a drag and drop. With jQuery it is very easy :-)

Answer 2

DFectuoso, if you have proper db indexes, you should go with the database sort and just store the order in a separate table, file on disk, memcached - take your best pick.

The reason I would go with this solution is that, if you data set gets larger, your reordering/reinserting algorithm would potentially take a long time and you'd have to reorder all of your items.

If you let the db handle the ordering, you can request only a small portion of the items and the db will use an index and wouldn't have to reorder everything, which ideally is very fast.

Otherwise, you could opt in for storing this data in any format you want - serialize the PHP object and store it on disk if you fancy that.

Answer 3

I'm thinking about this problem, and the solution I came up is having a decimal number as order and change the number of the item change for a number between the next and the previous item

Order    Item
-----    ----
1        Original Item 1
2        Original Item 2
3        Original Item 3
4        Original Item 4
5        Original Item 5

If you change the item 4 to the 2nd position, you get:

Order    Item
-----    ----
1        Original Item 1
1.5      Original Item 4
2        Original Item 2
3        Original Item 3
5        Original Item 5

If you change the item 3 to the 3rd position, you get:

Order    Item
-----    ----
1        Original Item 1
1.5      Original Item 4
1.75     Original Item 3
2        Original Item 2
5        Original Item 5

Theoretically there is always a decimal between two decimals, but you could face some storage limits.

What do you (stackers) think of this solution?

Answer 4

Here is a solution off the top of my head. It is untested but should point you in the right direction. Have an INT 'order' column in the table you want to sort.

The HTML/javascript (jquery-ui)

<!-- This is the list of items where the item's data base id is in the element
id, i.e. id="item-DATABASE_ID".  These items can be displayed with PHP by
SELECTing them from the database using ORDER BY order ASC -->
<ul id="sortable">
    <li id="item-1">Foo</li>
    <li id="item-2">Bar</li>
    <li id="item-3">Baz</li>
</ul>

<script type="text/javascript">
$(function() {
    // This turns the list into a jquery UI sortable list
    $("#sortable").sortable({
        // This even fires whenever the list is changed
        change: function(event, ui) {
            // This sends the list data to an  AJAX handler
            $.post({
                url: 'path_to_ajax_handler.php',
                // The serialize function transforms the list information
                // into a query string i.e.: item[]=1&item[]=2&item[]=3
                data: $('#sortable').sortable("serialize")
            });
        }
    });
});
</script>

The PHP

<?php
// The IDs of the items to be sorted are passed to the AJAX handler in the order
// they are listed in the page
if (isset($_POST['item']))
{
    $items = (array)$_POST['item'];

    // This updates all the items in the table with the correct order
    for ($x = 0; $x < count($items); $x++)
    {
        $query = "UPDATE * FROM orderable_things SET order = $x WHERE id = ".$item[$x];
        mysql_query($query);
    }
 }

Answer 5

I would do something similar to Zendesk.

You click a button saying "Edit Order", and when you're done say "Save Order" or something to that effect. These can be Ajax calls to make it more usable, but can also be page refreshes, allowing for degrading gracefully.

So it would be some thing like:

<ul>
<li><input type="hidden" name="item1" value="1" />Item 1</li>
<li><input type="hidden" name="item2" value="2" />Item 2</li>
<li><input type="hidden" name="item3" value="3" />Item 3</li>
<li><input type="hidden" name="item4" value="4" />Item 4</li>
</ul>

When you move the items around, this would change the value of the hidden field. When you post this for you could use those values in an update statement, making one call only. Something like:

UPDATE `things` SET `order` = $value WHERE `name` = $name;

It might not be efficient in this case as there would be 4 SQL statements, although there might be another way to solve this.

I think the main advantage is that you would always have a logical order of 1,2,3,4 instead of anything else if your JavaScript is well written.