Home » AJAX » Using jQuery to Reorder a List and Update a Database

Using jQuery to Reorder a List and Update a Database

Posted by John Ward on Sep 30, 2009 in AJAX, PHP Tutorials, Web Development Tutorials | 38 comments

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:

A web server that handles PHP and MySql or setup a local Windows Apache MySql Php web server.
jQuery and jQuery UI.
Basic HTML/CSS/Javascript knowledge.

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.

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:

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

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:

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.

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.

I hope this tutorial helped someone out

http://www.pspgoguide.com/ PSP Go

Wow this is a really detailed report. Thanks for filing it, it could be very useful in future.
http://aviralsharma.com Aviral Sharma

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
http://www.teamtutorials.com John Ward

Aviral is right, I will change the sql.
http://myresortweek.com resort for couples

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.

http://www.teamtutorials.com John Ward

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

http://www.r4-revolution.co.uk R4 revolution

Im looking for a way to have javascript or jquery do a mouse click on an object or a certain location. Can anyone help?
http://www.r4-revolution.co.uk r4ds card

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.
http://craigkendall.com Craig

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?
http://craigkendall.com Craig

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

http://craigkendall.com Craig

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.
http://kitap.cumhuriyeti.com.tr tolga

thanks for the nice tutorial, applied step by step with minor changes, works like a charm. Good work…
http://www.freebeat.org free beats

thank you very useful
http://www.cetvn.com maicle

Great idea, thanks a lot
http://www.changolounge.com maicle

Query is very important in create a system. thanks for sharing
http://www.sherayapi.com söve

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…
http://www.gcommerce.com.au/ web design brisbane

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?
http://finnartafc.com/ football

Is it possible to update database using front page without MS access in the system?
http://finnartafc.com/ football

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?
http://www.johnathanward.com John Ward

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.
http://reggi.com Thomas Reggi

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……?
http://rainrain,com byronyasgur

brilliant, thanks

jaburnik

yeah brilliant!

jaburnik

slick rick yow!

Using jQuery to Reorder a List and Update a Database

Sponsors

Related Posts

Our Sites

Recent Posts

Recent Comments

Meta

Follow Us!

Using jQuery to Reorder a List and Update a Database

Sponsors

Related Posts

Our Sites

Tags

Recent Posts

Recent Comments

Meta