This is a combination of jQuery and a PHP problem so I’m not 100% certain where to post it.
I am working on method to reoder a list that comes from a database. When the lists is displayed, it will be ordered by a “sort” column. The user can drag/drop the list to change the order, and I want the order to update the sort values in the database.
I found a script that provides a decent starting point, but I am not getting the results I want.
1: It is able to reorder the columns, and save the results which is good.
2: It is saving the results as “0,1,2,3” rather than reordering the numbers from the “sort” column. (< this is what I need fixed)
EG: Name > Sort
Apple 3
Orange 5
Pear 13
Kiwi 105
If I reorder these in the reverse order using the script, I end up with the following in the database.
Apple 3
Orange 2
Pear 1
Kiwi 0
Yet what I want is,
Apple 105
Orange 13
Pear 5
Kiwi 3
Are there any suggestions how I can get it to work with the existing “sort” values, rather than serializing and starting at 0.
Here are the script files I am currently working with for testing.
DATABASE
CREATE TABLE `menu` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(100) default NULL,
`sort` int(2) NOT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `menu` (`id`, `title`, `sort`) VALUES
(1, 'Apple', 3),
(2, 'Orange', 5),
(3, 'Pear', 13),
(4, 'Kiwi', 105);
MAIN FILE (menu_list.php)
<!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="https://ajax.googleapis.com/ajax/libs/jquery/1.4/jquery.min.js"></script>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.11/jquery-ui.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("localhost", "username", "password") or die ('Cant Connect to MySQL');
// Selecting Database
mysql_select_db("database_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'] . "</li>\
";
}
?>
</ul>
</body>
</html>
UPDATE SCRIPT (sort_menu.php)
<?php
// Connecting to Database
mysql_connect("localhost", "username", "password") or die ('Cant Connect to MySQL');
// Selecting Database
mysql_select_db("database_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());
}
?>