JQuery Sortables with PHP and MySQL

Submitted by Mike Milano on June 27, 2008 - 1:00am.

This article will show you how to order a server-side list of fruit from a database using drag and drop behavior and then save them back to the database in order.

I recently worked on a project which required an unordered list to be re-ordered and then saved. I wasn't about to make users enter numbers next to each item so I looked toward JQuery's UI library.

Objective: We will display a list of fruit. The user can then drag the fruit and click a button to save the new order in the database.

For this example, let's use a database table named fruit with the following fields: id, name, weight.

Schema:

CREATE TABLE fruit (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(64) NOT NULL,
weight TINYINT UNSIGNED NOT NULL
) TYPE = MYISAM ;
 
INSERT INTO fruit (name, weight) VALUES ('apple', 1);
INSERT INTO fruit (name, weight) VALUES ('pear', 2);
INSERT INTO fruit (name, weight) VALUES ('orange', 3);

I'll begin with some very basic PHP to setup the scenario.

This code queries the table ordered by weight. It then loops through and prints the result between list tags.

<ul id="fruit_list">
 
  <?php // query fruit table and print out each item
  $result = mysql_query("SELECT id, name, weight FROM fruit ORDER BY weight");
 
  // print the list items
  while ($row = mysql_fetch_assoc($result)) {
    echo "<li>". $row['name'] ."</li>\n";
  }
  ?>
 
</ul>

We're printing a list of fruit from the database ordered by weight. Now how do we re-order the list of fruit? Simple, JQuery Sortables.

JQuery isn't going to do everything automatically. We still need to setup the form with input fields to submit the data. Let's take the last code block and add in hidden form elements which contain the value of each fruit ID.

<form method="POST" action="<?php echo $_SERVER['PHP_SELF']?>">
 
<ul id="fruit_list">
  <?php // query fruit table and print out each item
  $result = mysql_query("SELECT id, name, weight FROM fruit ORDER BY weight");
 
  // print the list items
  while ($row = mysql_fetch_assoc($result)) {
    echo '<li>
                 <input type="hidden" name="fruit[]" value="'. $row['id'] .'" />
               '. $row['name'] .'</li>';
  }
  ?>
</ul>
 
</form>

Notice we used fruit[] as the field name for all the rows. When the form is submitted,l the fruit ids will be put into a PHP array in the order they appear on the form. i.e. $_POST['fruit']. Since PHP already handles that for us, we just need a way to move the list items around.

You will need to download both JQuery and the JQuery UI. (yes, they are separate)

You can download the full package, but for sortables, you only need these components: UI Core, Draggable, and Sortable.

Let's finish our script by adding the JQuery and some PHP to handle the sort and update the database.

Full Script (tested)

<?php
// make your db connection up here ...
$link = mysql_connect('localhost', 'user', 'pass');
$db = mysql_select_db('dbname', $link);
 
// handle POST
if ($_POST) {
  // use $i to increment the weight
  $i=1;
  // loop through post array in the order it was submitted
  foreach ($_POST['fruit'] as $fruit_id) {
    // update the row
    $result = mysql_query("UPDATE fruit SET weight=". $i . " WHERE id=". mysql_real_escape_string($fruit_id));
    // increment weight to make the next fruit heavier
    $i++;
  }
}
?>
<script type="text/javascript" src="jquery-1.2.6.js"></script>
<script type="text/javascript" src="jquery-ui-1.5.1.min.js"></script>
<script type="text/javascript">
// when the entire document has loaded, run the code inside this function
$(document).ready(function(){
  // Wow! .. One line of code to make the unordered list drag/sortable!
  $('#fruit_list').sortable();
});
</script>
<form method="POST" action="<?php echo $_SERVER['PHP_SELF']?>">
 
<ul id="fruit_list">
  <?php // query fruit table and print out each item
  $result = mysql_query("SELECT id, name, weight FROM fruit ORDER BY weight");
 
  // print the list items
  while ($row = mysql_fetch_assoc($result)) {
    echo '<li>
                 <input type="hidden" name="fruit[]" value="'. $row['id'] .'" />
               '. $row['name'] .'</li>';
  }
  ?>
</ul>
 
<input type="submit" name="reorder" value="Re-Order Fruit" />
 
</form>

And there we have it. Now when you submit the form, it should re-sort your fruit, update the db, and the fruit list should be displayed in the new order after submission.

Be sure to take a look at the JQuery Sortables documentation to see how much you can customize the behavior of the sort. With that and a little CSS, you can make this UI pretty sharp.

I believe I tested that code

Submitted by Mike Milano on October 5, 2008 - 7:33am.

I believe I tested that code when I wrote this. There's quite a few elements to this article, so maybe you could provide more info as to which part you're having a hard time with.

I can't get this to work.

Submitted by Anonymous (not verified) on October 5, 2008 - 5:08am.

I can't get this to work. Could you provide a working demo?
Thanks

Coder1