Using jQuery to Reorder a List and Update a Database

You should not use this code on a production website.

Warning: This tutorial uses old techniques. It is insecure and will leave your server vulnerable to SQL Injection attacks.This tutorials also uses mysql_ functions that are no longer support. For updated tutorials look for a PDO or MySQLi tutorial.This post will be delete or revised in the future.

This tutorial will show you how to use jQuery to allow as list (UL) to be reordered and will also make an AJAX call to a PHP file that will update the database when an item is dropped. I found this useful for a project I was working on recently. The user wanted to be able to change the order that categories displayed in a report. The items are stored in a database with a field called display_order. When the report is generated we simply order by that field.

This tutorial requires:

First you are going to need to download jQuery Ui. There are plenty of feature within jQuery User Interface that I will not be touching in this tutorial, but for sake of the tutorial download the entire bundle. Also I will be using the default (UI Lightness) theme.
Create a folder for this project on your server. You will need to unzip the jQuery files and put them all in this directory.
jquery-reorder-tutorial-01

Since jQuery comes with an example called index.html I am going to name my file test.php. You can call it whatever you want. Create the file. We will use it later. We’ll set the database up first.

I like to use HeidiSQL when working with MySQL. Now on the database server I am going to create a database call test_tutorials, with a table called report_items. Here are the field properties for the report_items table:
jquery-reorder-tutorial-02

Now populate your table with some test data. I’ll insert 6 rows. Also make sure you insert the display order starting from 0.
jquery-reorder-tutorial-03

You can save the changes to your data base and get back to working on test.php. Open the file. We’ll start with the basic layout.

<!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>Reorder Test</title>
</head>

<body>

</body>
</html>

Next we’ll include the stylesheet from our jQuery theme. I’ll also add some CSS from the example on the jQuery UI website. Then we’ll include the jQuery javascript files.

<!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>Reorder Test</title>
</head>

<link rel="stylesheet" type="text/css" href="development-bundle/themes/base/ui.all.css"/>

<style type="text/css">
	#sortable { 
		list-style-type: none; 
		margin: 0; 
		padding: 0; 
		width: 60%; 
	}
	#sortable li { 
		margin: 0 3px 3px 3px; 
		padding: 0.4em; 
		padding-left: 1.5em; 
		font-size: 1.4em; 
		height: 18px; 
	}
	#sortable li span { 
		position: absolute; 
		margin-left: -1.3em; 
	}
</style>

<script type="text/javascript" src="js/jquery-1.3.2.min.js"> </script>
<script type="text/javascript" src="js/jquery-ui-1.7.2.custom.min.js"></script>

<body>

</body>
</html>

We are going to pull the intial data from the database. In order to do this we’ll need to make a database connection. Create a new file called connect.php and create a database connection (obvious added your credentials where needed.

<?php

$servername='localhost'; //no need to change if the file is located on the same server
$dbusername='username'; //your db username
$dbpassword='password'; //your db password
$dbname='database'; //for this tutorial I called my db test_tutorials

connecttodb($servername,$dbname,$dbusername,$dbpassword);
function connecttodb($servername,$dbname,$dbuser,$dbpassword){
	global $link;
	$link=mysql_connect ($servername,$dbuser,$dbpassword);
	if(!$link){die("Could not connect to MySQL");}
	mysql_select_db($dbname,$link) or die ("could not open db".mysql_error());
}


?>

Save connect.php and include it into your test.php file:

<?php include('connect.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>Reorder Test</title>
</head>

<link rel="stylesheet" type="text/css" href="development-bundle/themes/base/ui.all.css"/>

<style type="text/css">
	#sortable { 
		list-style-type: none; 
		margin: 0; 
		padding: 0; 
		width: 60%; 
	}
	#sortable li { 
		margin: 0 3px 3px 3px; 
		padding: 0.4em; 
		padding-left: 1.5em; 
		font-size: 1.4em; 
		height: 18px; 
	}
	#sortable li span { 
		position: absolute; 
		margin-left: -1.3em; 
	}
</style>

<script type="text/javascript" src="js/jquery-1.3.2.min.js"> </script>
<script type="text/javascript" src="js/jquery-ui-1.7.2.custom.min.js"></script>

<body>

</body>
</html>

Now we need to retrieve the database results and dsiplay them. The items will be display in an un-ordered list. We’ll add this code to the body.

<body>
	<ul id="sortable">
    <?php
	$sql = "select * from report_items order by Category_Display_Order ASC";
	$query = mysql_query($sql);
	
	while($row = mysql_fetch_array($query))
	{
		$catid = $row['Category_ID'];
		$catname = $row['Category_Name'];	
		echo "<li id='item_$catid' class='ui-state-default'><span class='ui-icon ui-icon-arrowthick-2-n-s'></span>$catname</li>";		
	}

	?>    
    </ul>
</body> 

Take not above. When we echo the li we are adding the ui-state-default class which is just a jQuery theme class and the span that we included is an arrow icon also provide with jQuery Ui. Where you see item_$catid, that should be your primary key from your database (this will be used later to reorder items.

Now save the file and load it in your browser. If you followed everything hopefully you will get something like this:
jquery-reorder-tutorial-04

Now we’ll enable the sortable function on our list. Remeber I gave the UL tag an id of sortable. Add this code in the head below your jquery javascript files:

<script type="text/javascript">
	$(function() {
		$("#sortable").sortable({
			placeholder: 'ui-state-highlight',														
		});
		$("#sortable").disableSelection();
	});
</script>

The code above enables sorting of the UL with the id #sortable. The placeholder: ‘ui-state-highlight’ tells jquery we want it to show a place holder of where we can drop the item. In the them I am using it is a yellow box. Make the changes above and save your page. You should now be able to drap and drop your list items.
jquery-reorder-tutorial-05

Now that you have that working we can move to the AJAX call. When an item is dropped, we want to call a php file to update the database. We do that by using the stop: action. So when dragging stops, the script will call the url (updatedb.php) we provided below. To make the AJAZ called, we let jQuery do the work.

<script type="text/javascript">
	$(function() {
		$("#sortable").sortable({
			placeholder: 'ui-state-highlight',
			stop: function(i) {
				placeholder: 'ui-state-highlight'
				$.ajax({
					type: "GET",
					url: "updatedb.php",
					data: $("#sortable").sortable("serialize")});
			}														
		});
		$("#sortable").disableSelection();
	});
</script>

You see the ajax function above. We tell it what type of request, in this case wer’ll use get variables. We also tell it what URL, this is a php file we’ll create to update the database. Then we add the data that will be sent.

The code $(“#sortable”).sortable(“serialize”)}); will take the items within our ordered list and serialize it. It will take the items of the list, in whatever order they are in, and turn them into a get variable. So for each item it will add it to an array and format is properly to make the call to the PHP file. The data will look like this:
item[]=1&item[]=2&item[]=3&item[]=4&item[]=5&item[]=6

If you want to debug AJAX request I recommend using Firefox with the Firebug plugin. If you want to see that the request is being attempted and that what the data looks like, you can add an alert box just below the placeholder like this: alert($(“#sortable”).sortable(“serialize”));
Save the changes to you file we are done with test.php. Here is my full source of test.php up to this point.

<?php include('connect.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>Reorder Test</title>
</head>

<link rel="stylesheet" type="text/css" href="development-bundle/themes/base/ui.all.css"/>

<style type="text/css">
	#sortable { 
		list-style-type: none; 
		margin: 0; 
		padding: 0; 
		width: 60%; 
	}
	#sortable li { 
		margin: 0 3px 3px 3px; 
		padding: 0.4em; 
		padding-left: 1.5em; 
		font-size: 1.4em; 
		height: 18px; 
	}
	#sortable li span { 
		position: absolute; 
		margin-left: -1.3em; 
	}
</style>

<script type="text/javascript" src="js/jquery-1.3.2.min.js"></script>
<script type="text/javascript" src="js/jquery-ui-1.7.2.custom.min.js"></script>
<script type="text/javascript">
	$(function() {
		$("#sortable").sortable({
			placeholder: 'ui-state-highlight',
			stop: function(i) {
				placeholder: 'ui-state-highlight'
				$.ajax({
					type: "GET",
					url: "updatedb.php",
					data: $("#sortable").sortable("serialize")});
			}														
		});
		$("#sortable").disableSelection();
	});
</script>
<body>
	<ul id="sortable">
    <?php
	$sql = "select * from report_items order by Category_Display_Order ASC";
	$query = mysql_query($sql);
	
	while($row = mysql_fetch_array($query))
	{
		$catid = $row['Category_ID'];
		$catname = $row['Category_Name'];	
		echo "<li id='item_$catid' class='ui-state-default'><span class='ui-icon ui-icon-arrowthick-2-n-s'></span>$catname</li>";		
	}

	?>    
    </ul>
</body>
</html>

Now we need to actually update the database. Create a file called updatedb.php. This file is pretty simple. We take each item in the array and then update the database based on the array position($key) and the primary key ($value)

<?php
include('connect.php');

foreach($_GET['item'] as $key=>$value) {
	mysql_query("UPDATE report_items SET Category_Display_Order = '$key' WHERE Categoryt_ID ='$value';");
}
?> 

Save that file and test the reorder. If you have problems and you think it is with the AJAX call. Check fire bug. Here is an example of a bad request in this case I tried to include a file that was not there.
jquery-reorder-tutorial-06

I hope this tutorial helped someone out

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join the TeamTutorials mailing list and get the latest tips, tricks, and special discounts for members only.

Thanks! Please click the link in the email we just sent you to confirm.

  • Wow this is a really detailed report. Thanks for filing it, it could be very useful in future.

  • For those troubled with running this program, just fix the updatedb.php to

    mysql_query(“UPDATE report_items SET Category_Display_Order = ‘$key’ WHERE Category_ID =’$value'”);

    just 2 syntax errors fixed and it works like charm.

    Thanks a lot for posting this script !!

    If it still doesn’t work for you, reach me at http://aviralsharma.com/contact.php

  • Aviral is right, I will change the sql.

  • Hi there, i’m using php and mysql. My problem is, after i have performed an update on my database, i cant see the changes immediately. I can only see after i restart my browser. How can i overcome this? Thank you.

    • You would need to make a function to load the initial data via ajax, then when the results are update call that function again.

  • Im looking for a way to have javascript or jquery do a mouse click on an object or a certain location. Can anyone help?

  • i have added new entries and revised others and now need to transfer this newer version back to the orginal network at work, aka update the old one, i tried to select all and copy and paste though it dosnt add the new entries.

  • First timer with jquery here. Making a list sortable for a client’s admin area using this. What I’m just not clear on is if some sort of trigger has to make the updatedb.php do it’s stuff.

    I have everything working on the drag and drop side, but it doesn’t appear to be updating the db.

    Does it do it with the AJAX as the items are dragged and dropped or is some trigger required to call to the updatedb.php and store in the db?

  • Okay, got it working… roughly. Now when I drag and drop. the items are storing new numbers to the database, but where the IDs start out as 1, 2, 3 the updated numbers look like 0, 0, 2 or similar.

    Any ideas?

    • Ade

      hi there, Have tried this tutorial and had the same issue as you Craig with duplicate numbers in the changed field – how did you fix this?? thanks

  • Got it again… guess I should wrestle just that one more minute each time, eh?

  • John

    Hi,

    I have the same problem as Craig’s first post.
    The drag and drop works, but its not triggering the updatedb.php file to safe te changes.

    how did you get it working Craig ?

    ty

  • John

    Damn, now it works for me too.
    I even started all over again to see what i changed, but appereantly if you enter the right info (db and tables) it should work.

    Ty for the great tutorial !!!

  • TonyB

    Hello.
    Thank for the post.
    My task was to reorder (using drag and drop) a list within a list, keeping the order numbers unique. After much head scratching, the way I done it was the manipulate the array the ajax sent me.

    Once I sussed out what I have to do with the order numbers, it was a piece of cake.

    Tank you again.

  • thanks for the nice tutorial, applied step by step with minor changes, works like a charm. Good work…

  • thank you very useful

  • Great idea, thanks a lot

  • Query is very important in create a system. thanks for sharing

  • It is a really detailed report.Thanks.

  • Lenka

    Thanks for the tutorial. I am going to use it for reordering photos in our photo gallery. I got it working without any problems.

  • Robert

    GREAT SOLUTION! Thank you very much!

  • Bob

    See, I let users on my website have there own table and they can have a list of there own. Now…when they delete an item, and add an item; sometimes two ID’s become the same and the next time they move it won’t move cuz there are two id’s same…

  • Is there a possibility of high resources utilization during the execution of the above processes? If yes, is it only executed during the schedule maintenance period?

  • Is it possible to update database using front page without MS access in the system?

  • Hi…I downloaded the jquery extension for dreamweaver and installed via adobe extension manager…yet when I go and create a new javascript page the $ function shows no code hints?

  • Update, I had to delete the demo and source code after some passwords were leaked. I plan to restore these files once I can get them out of backup.

  • The jquery ui css file is now named jquery.ui.all.css

  • YO

    Is it possible to get this order information into a row instead of in a column?

    • brandon

      why would you want to do that?

  • Bishal Bhatta

    Thanks.It helped me a lot in my project.

  • aguy

    Great tutorial, thanks.

    I had to make a couple of small tweaks to get it working:

    1) Updated stylesheet URL to

    2) Updated JQuery includes to

    3) Changed SQL syntax to:

    mysql_query(“UPDATE report_items SET Category_Display_Order = ‘$key’ WHERE Category_ID =’$value’”);

    Thanks

  • Paul B

    You really should disallow html in comments: quite a few of these are just spam. I’m not really sure I see why people would bother spending time leaving silly, meaningless comments on others’ blogs (I’m sure Google et al must recognised and tuned out any search weighting this would give to their lame sites years ago) but they do.

  • haribabu

    Using jQuery to Reorder a List and Update a Database …
    code vry nice ……

    I have one doubt….. Suppose In my database 200 records are there but i want to display only 30 records in browser and if i click next means,next 30 records has to display……..HOW…?

  • Joe

    Great tutorial, thanks!

    Only problem I’m getting is that is seems to be slow to save the new order of the list. I’m trying to sort a few more items, say about 25, but it is taking up to 30 seconds?

    Anyone else had that problem?

  • hari

    I have one doubt….. Suppose In my database 200 records are there but i want to display only 30 records in browser and if i click next means,next 30 records has to display…….any one can help me……?

  • brilliant, thanks

    • jaburnik

      yeah brilliant!

  • jaburnik

    slick rick yow!

  • Khan

    why you removed the demo and source file?? please upload asap…

  • domi

    awesome!!!! thank you sooo much!
    very easy to understand! …i needed it in asp and had no trouble what so ever understanding it!
    thx

  • thank you
    very clear

  • Aw, this was a very good post. Taking the time and actual
    effort to make a top notch article… but
    what can I say… I put things off a lot and never manage to get nearly anything done.

  • When I originally commented I clicked the “Notify me when new comments are added” checkbox and now each time a comment is added I get several e-mails with the same comment.
    Is there any way you can remove me from that service? Appreciate it!

  • How consumers motivated will he be hiring. Risky or SafeIs it a
    point of having it completed on past energy use. Selecting
    the right person. If you plan to build an ark of Biblical dimensions,
    and interior decoration, coloring, whereas if you are properly initiated without indirect cash.

    my weblog … web page (Dannie)

  • I think the admin of this website is in fact working hard for his web site, for the reason that here every information is quality based stuff.

  • Wow, awesome weblog structure! How lengthy have you
    been running a blog for? you made running a blog glance easy.
    The entire glance of your website is excellent, as smartly as the content!

  • Hi friends, its impressive article abot cultureand completely explained, keep it up all
    the time.

  • I quite like reading an article that can make people think.

    Also, many thanks for permitting me to comment!

    Here is my website … trucks in Valdosta ga for sale

  • ” goes the old joke, “Annd don’t tell me ‘eat right and exercise’ –
    I did that for a whole day and it doesn’t work. After all, no amount of weight loss and toning, even if it rivals celebruty weight loss, is
    worth risking your health and your safety for the long-term.
    One may also bears burning pain in legs, feet, arms and hands.

    my blog post … pillole green coffee funziona

  • There are some great products on the market today that can help you stay
    cool oon even the hottest of days. That’s why people have used it in places where
    smoking is ban. By removing the material that absorbs the liquid nicotine,
    a consumer is able to remove the mouthpiece and put several drops of e-liquid
    onto the part that they call the atomizer bridge.

    My weblog: sigaretta elettronica costi