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.
- 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`) )
- 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 & 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 problem, leave ur comment !
[...] Sorting items on the fly (AJAX) using jQuery UI Sortable, PHP … [...]
[...] Sorting items on the fly (AJAX) using jQuery UI Sortable, PHP & MySQL. | Hdeya team blog Great technique for sorting with AJAX [...]
Hope you can write a tutorial like:
“Create a simple igoogle with jquery” > drag n drop, then save to mysql database through php
is it possible? Prove to me within one day if its possible
(challenge u, deadline, 10 May 2009)
The Above Tutorial can be used to make simple iGoogle
!!
so, try to provide more information about your needs so as we can help you
Just what I needed, thanks alot
Confused on something though. On your page called with ajax, there is this line:
$menu = $_POST['menu'];
where is it getting the name “menu” in the POST? I don’t see any reference to it.
on menu_list.php, round line 15, you’ll find the following :
url: “sort_menu.php”,
type: “post”,
so, it send the sorted array as POST data to “sort_menu.php”
still doesnt makes sense where the $_POST["menu"] variable is beeing created..
This got me hung up too. I think this is where ‘menu’ is posted from:
echo ” .
I guess the syntax looks for an id and everything after the “_” is what gets posted.
HI,could you tell me how to update element between two div?
Can you explain in more details, please ?
Any chance that you can provide a classic ASP sample of sort_menu.php…?
Converting menu_list.php to an ASP version is not a problem at all, but i really don’t know how to read the array in ASP like you’re doing from line #8 in sort_menu.php.
Thanks in advance
Dear daj, Sorry but we’re not using ASP.
Hi,
is it possible to post more data than only one array? Or add some data tho this array before post to php?
I have more than one of these sortable div and i’d like to add the possibility to move elements of one sortable div to another sortable div.
How would you solve this problem?
Regards
Colin
Hi there!
nice tutorial, I’ve used with Drupal and works really fine thx!
I own you a beer
// Great tutorial but … please fix this SQL INJECTION style mysql_query
!!!!!!!!! SQL INJECTION via $menu[$i] !!!!!!!!!!
mysql_query(“UPDATE `menu` SET `sort`=” . $i . ” WHERE `id`=’” . $menu[$i] . “‘”) or die(mysql_error());
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
$menu[$i] variable is send by the client so it’s insecure …
what if I send post … $menu[$i] = blabla’ or 1=1;# … this will update all records in this table …
!!!!!!!! CORRECT !!!!!!!!!!!!!
mysql_query(“UPDATE `menu` SET `sort`=” . mysql_real_escape_string($i) . ” WHERE `id`=’” . mysql_real_escape_string($menu[$i]) . “‘”) or die(mysql_error());
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Great tutorial 10x again
Since you didn’t include your exact versions of compiled jQuery and jQuery UI, the script gives me “there’s an error with AJAX.” I appreciate the tutorial and don’t want to disparage you taking the time to share your work freely with the world, but if you’re going to write a tutorial, put up all the files required, preferably as a downloadable ZIP, and take the time to explain the code.
Before asking for help, you should use real email, and you’re using FAKE ONE, so how can we help you ?!
Thanks for this tutorial!!!!!
Thanks. I will use this in my CMS.