Creation of a sortable list with Ajax tutorial

April 21, 2008 • Ajax, Php, Tutorials by Emanuele Feronato • 28 Comments

One interesting Web 2.0 effect is the sortable list, and we are going to make one in less than five minutes thanks to Prototype and script.aculo.us libraries.

Prototype is a JavaScript Framework that aims to ease development of dynamic web applications and can be downloaded here, while script.aculo.us (scriptaculous from now on) provides you with easy-to-use, cross-browser user interface JavaScript libraries to make your web sites and web applications fly and can be downloaded here.

This tutorial is based on zen of shen’s pseudocode found at script.aculo.us Ajax Sortable Lists Tutorial.

This tutorial will show real code and a step by step guide through MySql, php and Ajax coding in order to create your sortable list.

Of course your hosting plan must allow you to use php and MySql, so refer to your hosting provider if you don’t know if your web space supports those features.

MYSQL

First we need to work on the database to create the table that will store our list. I made the simplest table ever, because it’s just an example, but you can add as many columns as you want, in order to fit the table to your needs.

My table only has three fields: the primary key id, the name of the list element color and the core column, the one where you will save the order of the elements, called, in a lack of creativity, color_order.

CREATE TABLE `sortable` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`color` TEXT NOT NULL ,
`color_order` INT NOT NULL 
) ENGINE = MYISAM ;

CREATE TABLE `sortable` (

`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,

`color` TEXT NOT NULL ,

`color_order` INT NOT NULL

) ENGINE = MYISAM ;

Once we have the table, let’s populate it. I am doing it directly with MySql, but again you are free to code your own application to populate it

INSERT INTO `sortable` ( `color` ) 
VALUES (
'Red'
), (
'Yellow'
), (
'Green'
), (
'Blue'
), (
'Black'
), (
'White'
), (
'Orange'
), (
'Purple'
)

INSERT INTO `sortable` ( `color` )

VALUES (

'Red'

), (

'Yellow'

), (

'Green'

), (

'Blue'

), (

'Black'

), (

'White'

), (

'Orange'

), (

'Purple'

)

Now the table looks like this one

and our MySql task is completed. Now, let’s write some php and html

PHP/HTML

<?php

$connection = mysql_connect (localhost, "your_login", "your_password");
mysql_select_db("your_db",$connection) or die("no db found");

$sql = "select * from sortable order by color_order asc";
$result = mysql_query($sql)or die(mysql_error());
$number = mysql_num_rows($result);

for($x=1;$x<=$number;$x++){
	$row = mysql_fetch_array($result);
	$list.= "			<li id=\"item_$row[id]\">$row[color]</li>\n";
}

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
	<head>
		<script type="text/javascript" src="prototype-1.6.0.2.js"></script>
		<script type="text/javascript" src="scriptaculous/src/scriptaculous.js"></script>
		<style type = "text/css">
			ul#sortlist{list-style:none;padding:0px;margin:0px}
			ul#sortlist li{padding:5px;border:1px solid #666666;margin:1px;font:normal 10px verdana;width:200px;color:#666666;background-color:#f0f0f0;cursor:move}
			#output{font:bold 10px verdana}
		</style>
	</head>
	<body>
		<ul id = "sortlist">
<?php echo $list; ?>
		</ul>
		<div id = "output">Drag to sort your favourite colors</div>
		<script type="text/javascript">
			Sortable.create('sortlist',{
				onUpdate:function(){
					new Ajax.Updater('output','sort.php',{onComplete:function(request){}, parameters:Sortable.serialize('sortlist'), evalScripts:true, asynchronous:true})
				}
			})	
		</script>
	</body>
</html>

<?php

$connection = mysql_connect (localhost, "your_login", "your_password");

mysql_select_db("your_db",$connection) or die("no db found");

$sql = "select * from sortable order by color_order asc";

$result = mysql_query($sql)or die(mysql_error());

$number = mysql_num_rows($result);

for($x=1;$x<=$number;$x++){

$row = mysql_fetch_array($result);

$list.= " <li id=\"item_$row[id]\">$row[color]</li>\n";

}

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

<head>

ul#sortlist{list-style:none;padding:0px;margin:0px}

ul#sortlist li{padding:5px;border:1px solid #666666;margin:1px;font:normal 10px verdana;width:200px;color:#666666;background-color:#f0f0f0;cursor:move}

#output{font:bold 10px verdana}

</style>

</head>

<body>

<?php echo $list; ?>

</ul>

<div id = "output">Drag to sort your favourite colors</div>

Sortable.create('sortlist',{

onUpdate:function(){

new Ajax.Updater('output','sort.php',{onComplete:function(request){}, parameters:Sortable.serialize('sortlist'), evalScripts:true, asynchronous:true})

}

})

</script>

</body>

</html>

Lines 3-4: Connecting to the database. If you don’t know your username, password or database name, ask them to your hosting provider

Lines 6-8: Query to get all the colors ordered by their color order

Lines 10-13: Building the list from the result of the query just executed

Lines 19-20: Including Prototype and scriptaculous sources. Remember to include Prototype before Scriptaculous and also remember to change the path if you placed the scripts in a different location

Lines 21-25: Giving the list a touch of style

Lines 28-30: Displaying the list

Line 31: Creation of the div element to display Ajax output

Lines 32-38: Calling the Sortable.create function to initialize the sortable list. You can find more information about Sortable.create function in the scriptaculous wiki at this link

And the php/html is over.

Now, I should made the Ajax part. But it’s already made by Prototype thanks to the Ajax.Updater object, so I just have to make the sort.php script called in the Ajax.Updater object

You can find more information about the Ajax.Updater object in the API Docs pages at this link

So it’s basically php again, but I will call this part the Ajax part

AJAX

Create a file called sort.php and write:

<?php

$connection = mysql_connect ("62.149.150.49", "Sql92833", "0015025a");
$db= mysql_select_db("Sql92833_5",$connection);

foreach ($_POST[sortlist] as $varname => $varvalue) {
	$sql = "update sortable set color_order = ".mysql_real_escape_string($varname)." where id = ".mysql_real_escape_string($varvalue);
	$result = mysql_query($sql) or die(mysql_error());
}

echo "You just updated it on ".date("Y-m-d H:i:s");

?>

<?php

$connection = mysql_connect ("62.149.150.49", "Sql92833", "0015025a");

$db= mysql_select_db("Sql92833_5",$connection);

foreach ($_POST[sortlist] as $varname => $varvalue) {

$sql = "update sortable set color_order = ".mysql_real_escape_string($varname)." where id = ".mysql_real_escape_string($varvalue);

$result = mysql_query($sql) or die(mysql_error());

}

echo "You just updated it on ".date("Y-m-d H:i:s");

Lines 3-4: Connecting to the database, just like in the php/html part

Lines 6-9: Scanning all the sortlist array passed with POST method by the Sortable.create and the Ajax.Updater and updating the color_order column of the sortable table according to sortlist names and values

Line 11: displaying an output message.

And that’s it! Drag colors as you want, then reload the page in the iframe and the colors will be in the same position you just ordered… unless someone else changed their order while the page was reloading

That’s all… hope you will find it useful as I did.

Rate this post: 1 Star

(28 votes, average: 4.50 out of 5)

Loading ...

Be my fan on Facebook and follow me on Twitter! Exclusive content for my Facebook fans and Twitter followers

This post has 28 comments

Sunil Patel

on April 22, 2008 at 12:31 pm

Awesome! Thats pretty cool. But, considering its Ajax you didnt really need an iframe :O
Emanuele Feronato

on April 22, 2008 at 12:43 pm

Sure, but I did not want people to reload the entire page to see it saves color order
Sunil Patel

on April 22, 2008 at 2:23 pm

No, thats not what I mean. You can use AJAX to fetch the data from the page and use innerHTML to modify the contents of the page – without reloading. Although I suppose thats getting more complicated than the tutorial should be. Sorry, I love the tutorial.
Emanuele Feronato

on April 22, 2008 at 3:14 pm

That’s exactly what the script does.

You never have to reload the page, the database is updated as soon as you release the list element, without reloading anything.
Icecool

on April 22, 2008 at 5:21 pm

What is ajax Explain please?
Harmen

on April 22, 2008 at 5:28 pm

PHP part is not safe…
Go to: http://www.emanueleferonato.com/stuff/sortable/sort.php

Change line 7 to:
$sql = “UPDATE sortable SET color_order = ‘” . mysql_real_escape_string($varname) . “‘ WHERE id = ‘” . mysql_real_escape_string($varvalue) . “‘”;

What if $varvalue is this: ” || a = a ?
Take a look what happens:
UPDATE sortable SET color_order = $varname WHERE id = ” || a = a
If id is empty or if a = a, would return all id’s
Emanuele Feronato

on April 22, 2008 at 6:53 pm

fixed

thank you
Sunil Patel

on April 22, 2008 at 7:06 pm

AJAX is basically any server side scripting language + javascript. And it allows scripts to run without the need to refresh the page or naviate away,
Sodaplayer

on April 23, 2008 at 6:10 am

Hope ROYGBP will still be there when I come back
Shaun

on April 23, 2008 at 7:05 pm

YOU SHOULD MAKE THAT INTO A GAME
juha

on May 5, 2008 at 6:33 pm

This seems neat!
If I use this on table that have thousands of rows, but visible only 30 on the page does the ajax update each row’s order number or just couple?

Does this work if data is presented on a multicolumn html table?
jai

on May 12, 2008 at 2:28 pm

ajax is cool especially if you have dreamweaver cs3 with spry widgets (using ajax) you can add, such as accordions and drop-down menus and tabs.
have you used spry widgets before or do you just code it all?
flo

on June 6, 2008 at 6:35 pm

One of the BEST tutorials for sortable list with Ajax!!!

… and i readed a lot of them.

Thank you so much Emanuele!
Marc Falk

on June 18, 2008 at 1:19 am

Thanks Emanuele!

The best tutorial so far, and I’ve too read and crawled a lot of these, damn. Anyway, can you explain how I use this with div’s and not ul and li’s ?

Help is appreciated!

Tanks!
Marc
Ame

on June 25, 2008 at 6:58 pm

Is there an application that can be purchased that will do something like this for a business intranet?
Rob

on July 31, 2008 at 8:37 pm

How would you go about incorporating this code into a form?

I am curious to know..
Jessica

on August 7, 2008 at 3:42 am

I currently have a JSP page containing a and s with a inside each to let the user re-sort their items’ orders. I’m trying to now use Ajax to replace this old way to sort items. But I don’t really know how.

question 1: can I still use or does it have to be converted to in order to use Ajax ? I tried to keep my table, but it doesn’t seem to work.

question 2: when I tried using and my list is long, the list items do not stay within the that i want the items to stay within. Is there something i can do to keep those items within the but of course being able to drag drop those items to resort and to update my database?
Jessica

on August 7, 2008 at 3:45 am

Sorry… re-posting same comment/question above…

I currently have a JSP page containing a TABLE and TRs with a SELECT inside each TR to let the user re-sort their itemsâ€™ orders. Iâ€™m trying to now use Ajax to replace this old way to sort items. But I donâ€™t really know how.

question 1: can I still use TABLE and TRs or does it have to be converted to UL and LIs in order to use Ajax ? I tried to keep my table, but it doesnâ€™t seem to work.

question 2: when I tried using UL and LIs and my list is long, the list items do not stay within the DIV box that i want the items to stay within. Is there something i can do to keep those items within the DIV box, but of course being able to drag drop those items to resort and to update my database?
Erik

on August 26, 2008 at 2:18 am

is it possible to use this with table rows?
and if so what changes do I need to make to the code besides changins all li’s to tr’s and ul’s to table’s?
Kelly

on November 17, 2008 at 7:14 pm

I’ve copied all the code (tried Zen of Shens too) and for some reason the update script never gets executed. There are no js errors it just doesn’t execute the update script. Can you suggest anything I should look for?
Alex Langley

on November 21, 2008 at 2:49 am

Very helpful. More clear then zenofshen.
rvk

on May 15, 2009 at 10:38 am

Great piece of code!
I’m quite newbie to ajax. How can I add more functionalities like “add” or “delete” items? what function should I call for these?
Thanks in advance.
tony

on June 7, 2009 at 7:15 am

lines 10-13…
when the list items are being setup… I’d like to add an image path storred in MYSQL but have been unable to place the URL. I know the function I’ve created is returning the appropriate path.

Here’s what I have so far… I’ve been unable to add the path to the image for each li. How do I make the function call to return the path for each list item?

$list.= “$row[project]

\n”;
Jeff Fassnacht

on July 8, 2009 at 7:53 pm

Thank you for such a simple yet comprehensive tutorial. Well done!
Jasmondo

on July 10, 2009 at 12:33 pm

I have followed this tutorial to he letter, but it’s just not working for me! When I try to drag the blocks, it just highlights the text of the list rather that move anything. I run PHP 5.2.9. Is anyone else having the same problem?

Here’s my link: http://www.verbdesign.com/ajaxtest/contentlist.php
ComputerProgrammingTutorials

on August 6, 2010 at 6:38 pm

A good one.
ranji

on September 10, 2011 at 2:25 pm

nice tutorials thanks for sharing it
car rental chennai

on November 18, 2011 at 2:27 pm

nice tutorials thanks for sharing it

italian geek and PROgrammer

This post has 28 comments

Emanuele Feronato

Emanuele Feronato

Icecool

Harmen

Emanuele Feronato

Sodaplayer

Shaun

juha

flo

Ame

Rob

Jessica

Jessica

Kelly

Alex Langley

rvk