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

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 = ".$items[$x];
        mysql_query($query);
    }
 }

Answer 2

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 :-)