jQuery/PHP/MySQL - Reorder List, Save Results

**codamedia** · Apr 5, 2011 21:13

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

Code:

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)

Code:

<!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>\n";
}
?>
</ul>
</body>
</html>

UPDATE SCRIPT (sort_menu.php)

Code:

<?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());
}
?>

**codamedia** · Apr 6, 2011 15:18

This problem has been solved thanks to another script I found.

I haven't been able to analyze the differences yet (same set of tools "jQuery, PHP, MySQL") but I have found a solution.

User Tag List

Thread: jQuery/PHP/MySQL - Reorder List, Save Results

Thread Tools

Display

Hybrid View

jQuery/PHP/MySQL - Reorder List, Save Results

Bookmarks

Bookmarks

Posting Permissions