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:
- 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