Devirtuoso

In the last tutorial we covered how to reorder a list by dragging and dropping.  In this tutorial we will show how to save the order of the list via Ajax.

Where We Left Off

We should be at a point where we now know how to reorder a list.  If you haven’t read the previous tutorial or aren’t certain on how to reorder lists in jQuery, I encourage you to check out Beginners Guide to jQuery Sorting part 1.

Here is the code from the previous post:

What We’re Building

This time around we’re just going to take the current order of the list and save it to the database for future use.

Reworking the List

Previously we had a simple HTML un-ordered list.  This time we’re going to make it a little more complicated, with good reason of course.  In order to view the updates we make to the database, we first have to have a list  pulling from the database.  For this we will be using PHP and MySQL.

First we’re going to create a file called db.php.  In this file we’re going to do two things.  First connect to the database, and second select all the items from the database and create a list from them.  I’m not going to go into too much detail with the connection info.  If you want a little refresher, we went into more depth in our Beginners Guide to Using Ajax with jQuery post.

Here is the PHP code for connecting to the database:

I guess at this point it might be helpful to see what the database looks like.  We have 3 columns:

•  itemID (unique identifier),
• content (the text inside our list)
• Order (keep track of the order)

Now we’ll create a function that selects all the items from the database and outputs an un-ordered list.

Here is the PHP:

First we query the database with the mysqli object we created when we connected to the database.  If it returns results without errors then we can go ahead with making our list. 

Making the list isn’t too hard, we’re simply creating a variable that holds our list.  We then cycle through all of the items returned from the database and add it to the end of our variable.  After we have all the info in our variable, we display it for the world to see.  One special note, you’ll see that we are giving each <li> an id.  We’re going to use this when we get to the Ajax portion of the tutorial.  For now just know that the id is required.

The New HTML

Now that we have our db.php file ready to go, we can now make use of it in our main file.

First off we’re going to import the file to use it.  At the very top we’ll just put:

Now we can use the function displayAll() that we just created.  We’ll be replacing the un-ordered list with this function.  When the page is processed, the function will pull the data from the database and output a brand spankin’ new list.  Pretty sweet huh.

The jQuery

The jQuery changed a bit.  We’re adding a new update parameter to the sortable function.  This will be called whenever the order has been changed.

In the update function we’ll be doing 3 things.

• Prepping the info to be sent via Ajax
• Send the info via Ajax
• Display the new updated list

Here is the jQuery:

Prepping the Info

First thing we tackle is prepping the info by serializing it.  This just means that we’re taking the order of the list and preparing them in a way that they can be attached to a url.(e.g. www.domain.com?serialized=data )  For this serialize function to work we have to create ids for our list items in a particular way.  If I want an array called “item”  then I would give my li tags an id of item_1, item_2, item_3, etc. The serialize function will take the text before the underscore as the array name, and the number after as it’s value.  So a list with ids of item_1, item_2 would be serialized to item[]=1&item[]=2 .   When we created the function displayAll() we took this into consideration, and put the items ID value from the database after the underscore so we can reference it later.

Send Info

Next comes the Ajax.  We’re going to use the post function.  We simply have to give it a url, any data we want to send to the url and the function we want to call when we receive something back from the url.

Display New List

The function that handles the response simply just switches out the list’s HTML with the new HTML.

Updating the Database

The first thing we’re going to do is create a file called update.php.  This is the file that will be called by the Ajax.  In this file a couple of things are going to happen.

• Get the array sent in by the Ajax
• Cycle through each item in the array
• Update the database with the new value
• Display a new list

Here is the PHP:

Get the Array from Ajax

Since we used the post method in our jQuery, PHP’s $_POST variable will be populated by what we pasted in.  In our case it was the item array. It’s always good practice to make sure you’ll be receiving what you’re expecting.  In this case we’re expecting an array, so we check and make sure it is an array.

Cycle through the Array

After we’re sure we have an array on our hands, we cycle through each element in the array.  For each element we’re going to check and make sure the value is a number.  If we didn’t malicious users could pass in some harmful stuff.

Update the Database

After we have verified that array’s value is a number, we then pass it into our SQL statement.  The array is ordered in the same manner as the list was.  So the first item in the array is also the first item in the list.  The SQL will  then set the Order of the item to where it is in the array.

Display a New List

The code for this is pretty well the same as displayAll().  The only difference is that we’re leaving the <ul></ul> off, as these will already be in place, and we’re just replacing the innards.

That’s IT

That’s pretty well it.  Now we have our initial code that display’s our list.  Then our jQuery that passes on the order of the list via AJAX.  Then our update PHP file that checks to make sure the values are good, and passes them to the database.  After all that it passes the new list back to the jQuery to display.  Voila!

db.php :

Here is the HTML / jQuery / PHP:

update.php