JQuery Sortables with PHP and MySQL

Posted: June 27, 2008

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 POSTif ($_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.