I found a blog entry titled jQuery Sortable Ajax PHP Integration that was a good start. However, it didn't actually use MySQL to store the sorted data so I've gone ahead and added the database queries and updates.
First I created a simple table to store links (I'd like my client to order the links they enter through an admin interface).
CREATE TABLE links (
id int(10) NOT NULL auto_increment,
link_url varchar(50) NOT NULL,
link_text varchar(50) NOT NULL,
order_position int(2),
primary key(id)
);
Next is the PHP page that reads the link data from the table and displays the links based on the order_position column of the table.
index.php
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<script type="text/javascript" src="/js/jquery/jquery-1.2.3.min.js"></script>
<script type="text/javascript" src="interface.js"></script>
<script>
$(document).ready(
function() {
$("#sortme").Sortable({
accept : 'sortitem',
onchange : function (sorted) {
serial = $.SortSerialize('sortme');
$.ajax({
url: "sortdata.php",
type: "POST",
data: serial.hash,
// complete: function(){},
//success: function(feedback){ $('#data').html(feedback); }
// error: function(){}
});
}
});
}
);
</script>
</head>
<body>
<ul id="sortme">
<?php
// Connect to the database as necessary
$dbh = mysql_connect('127.0.0.1','username','password')
or die ("Unaable to connnect to MySQL");
$selected = mysql_select_db("sort_test",$dbh)
or die("Could not select sort_test");
$result = mysql_query('SELECT * FROM links ORDER BY order_position');
while ($row = mysql_fetch_array($result)) {
print '<li id="' . $row{'id'} . '" class="sortitem">' . $row{'link_text'} . "\n";
}
mysql_close($dbh);
?>
</ul>
</body>
</html>
This will make an Ajax call back to the server where PHP will update the database as you drag and sort the list data:
sortdata.php
<?php
$sortme = $_POST['sortme'];
// Connect to the database as necessary
$dbh = mysql_connect('127.0.0.1','username','password')
or die ("Unaable to connnect to MySQL");
$selected = mysql_select_db("sort_test",$dbh)
or die("Could not select sort_test");
for ($i = 0; $i < count($sortme); $i++) {
mysql_query("UPDATE links SET order_position=$i WHERE id=$sortme[$i]");
}
mysql_close($dbh);
?>
A little CSS certainly wouldn't hurt things. Here's some I found on another tutorial:
<style>
ul {
list-style: none;
}
li {
background: #727EA3;
color: #FFF;
width: 100px;
margin: 5px;
font-size: 10px;
font-family: Arial;
padding: 3px;
}
</style>
I wouldn't call this production ready code, but all the pieces are in place if you'd like to use jQuery with Ajax, PHP, and MySQL for a sortable list. Don't forget, you'll need to get jQuery itself and the interface.js file as well. Another thing to consider is alerting the user if the update fails or takes a long time.
