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 &amp;amp;amp;amp; 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>

    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