Sorting items on the fly (AJAX) using jQuery UI Sortable, PHP & MySQL.

While we were working on http://www.duettographics.com Administration control panel, they required the ability to sort their images in easy way, so after some googeling we find many separate tutorials for this. So, we decided to write one simple tutorial that can describe the whole thing.

In this tutorial, we’ll work on sorting menu items.

Requirements : sortable jQuery UI, PHP & MySQL server support.

  1. Create a new MySQL Database called `test_db` and a new table called `menu
    CREATE TABLE IF NOT EXISTS `menu` (
      `id` int(11) NOT NULL auto_increment,
      `title` int(11) NOT NULL,
      `sort` int(2) NOT NULL,
      PRIMARY KEY  (`id`)
    )
  2. Create new php file called “menu_list.php”, this file will be our main file to sort the menu items .
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Sorting Items on the fly using jQuery UI, PHP & MySQL</title>
    <script type="text/javascript" src="js/jquery-1.3.2.min.js"></script>
    <script type="text/javascript" src="js/jquery-ui-1.7.1.custom.min.js"></script>
    <script>
    $(document).ready(
    function() {
    $("#sortme").sortable({
    update : function () {
    serial = $('#sortme').sortable('serialize');
    $.ajax({
    url: "sort_menu.php",
    type: "post",
    data: serial,
    error: function(){
    alert("theres an error with AJAX");
    }
    });
    }
    });
    }
    );
    </script>
    </head>
    <body>
    <h1>Menu List</h1>
    
    <ul id="sortme">
    <?php
    // Connecting to Database
    mysql_connect($hostname, $user_name, $password) or die ('Cant Connceto to MySQL');
    
    // Selecting Database
    mysql_select_db($db_name) or die ('Cant select Database');
    
    // Getting menu items from DB
    $result = mysql_query("SELECT * FROM `menu` ORDER BY `sort` ASC") or die(mysql_error());
    while($row = mysql_fetch_array($result)) {
    echo '<li id="menu_' . $row['id'] . '">' . $row['title_en'] . "</li>\n";
    }
    ?>
    </ul>
    </body>
    </html>
  3. Create a new file for AJAX purpose to update sorting called “sort_menu.php”
    <?php
    // Connecting to Database
    mysql_connect($hostname, $user_name, $password) or die ('Cant Connceto to MySQL');
    
    // Selecting Database
    mysql_select_db($db_name) or die ('Cant select Database');
    
    $menu = $_POST['menu'];
    for ($i = 0; $i < count($menu); $i++) {
    mysql_query("UPDATE `menu` SET `sort`=" . $i . " WHERE `id`='" . $menu[$i] . "'") or die(mysql_error());
    }
    ?>
    

Finally, this should works fine with you, but If you faced any problem, leave ur comment !

Share and Enjoy:
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google Bookmarks
  • LinkedIn
  • MySpace
  • Netvibes
  • StumbleUpon

20 Responses to “Sorting items on the fly (AJAX) using jQuery UI Sortable, PHP & MySQL.”

  1. [...] Sorting items on the fly (AJAX) using jQuery UI Sortable, PHP … [...]

  2. [...] Sorting items on the fly (AJAX) using jQuery UI Sortable, PHP & MySQL. | Hdeya team blog Great technique for sorting with AJAX [...]

  3. phpnoob says:

    Hope you can write a tutorial like:
    “Create a simple igoogle with jquery” > drag n drop, then save to mysql database through php

    is it possible? Prove to me within one day if its possible :)

    (challenge u, deadline, 10 May 2009)

    • admin says:

      The Above Tutorial can be used to make simple iGoogle :) !!
      so, try to provide more information about your needs so as we can help you ;)

  4. hak says:

    Just what I needed, thanks alot :)

  5. Bryan says:

    Confused on something though. On your page called with ajax, there is this line:
    $menu = $_POST['menu'];
    where is it getting the name “menu” in the POST? I don’t see any reference to it.

  6. ekenfire says:

    HI,could you tell me how to update element between two div?

  7. daj says:

    Any chance that you can provide a classic ASP sample of sort_menu.php…?

    Converting menu_list.php to an ASP version is not a problem at all, but i really don’t know how to read the array in ASP like you’re doing from line #8 in sort_menu.php.

    Thanks in advance

  8. Colin O. Below says:

    Hi,

    is it possible to post more data than only one array? Or add some data tho this array before post to php?

    I have more than one of these sortable div and i’d like to add the possibility to move elements of one sortable div to another sortable div.

    How would you solve this problem?

    Regards
    Colin

  9. sholn says:

    Hi there!
    nice tutorial, I’ve used with Drupal and works really fine thx!
    I own you a beer ;)

  10. paceto says:

    // Great tutorial but … please fix this SQL INJECTION style mysql_query

    !!!!!!!!! SQL INJECTION via $menu[$i] !!!!!!!!!!
    mysql_query(“UPDATE `menu` SET `sort`=” . $i . ” WHERE `id`=’” . $menu[$i] . “‘”) or die(mysql_error());
    !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    $menu[$i] variable is send by the client so it’s insecure …
    what if I send post … $menu[$i] = blabla’ or 1=1;# … this will update all records in this table …

    !!!!!!!! CORRECT !!!!!!!!!!!!!
    mysql_query(“UPDATE `menu` SET `sort`=” . mysql_real_escape_string($i) . ” WHERE `id`=’” . mysql_real_escape_string($menu[$i]) . “‘”) or die(mysql_error());
    !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    Great tutorial 10x again

  11. Arne says:

    Since you didn’t include your exact versions of compiled jQuery and jQuery UI, the script gives me “there’s an error with AJAX.” I appreciate the tutorial and don’t want to disparage you taking the time to share your work freely with the world, but if you’re going to write a tutorial, put up all the files required, preferably as a downloadable ZIP, and take the time to explain the code.

  12. jordi says:

    Thanks for this tutorial!!!!!

  13. Thelleo says:

    Thanks. I will use this in my CMS.

Leave a Reply