How to order the elements with jQuery (2/2)

In the previous article we saw how the sortable method allows the sorting of a list of items through dragging. We just saw a “visual” implementation though. But if we want the sorting to be lasting, hence it remains the same even if we reload the page, we will have to go the extra mile.
In the practical case, we will extract the items from a table of the database in which we will save the id, the item name and the item order.
The change of order has then to be intercepted and sent by means of an asynchronous call to a file that will take care of updating the database.
As you can see by the example, if you change the order, it will remain unaltered even by reloading the page (unless there is another user that is using the example) since it is saved in the database as it will be illustrated in this article.

Creating the database

Let’s create a database and name it sortable.
In this database we create a table named list with three fields: id, item_name and item_order.
You can execute this query:

CREATE  TABLE `list` (
 `id`  int(4) unsigned NOT NULL auto_increment,
 `item_name`  varchar(100) character set utf8 NOT NULL,
 `item_order`  int(4) unsigned NOT NULL,
 PRIMARY  KEY  (`id`)
)  ENGINE=MyISAM;

Now we populate this table with the items that will be displayed:

INSERT  INTO `list` (`id`, `item_name`, `item_order`) VALUES
(1,  'Item 1', 0),
(2,  'Item 2', 1),
(3,  'Item 3', 2),
(4,  'Item 4', 3),
(5,  'Item 5', 4);

And in the end we create the file with the connection parameters db_config.php:

define("DB_HOST",  "localhost");
define("DB_NAME",  "sortable");
define("DB_USERNAME",  "root");
define("DB_PASSWORD",  "*********");

Creating support for server side operations

Great, at this point we can start developing the class that will take care of managing the server side operations, we will name it sortableSupport.php.

include_once  'db_config.php';

class  sortableSupport
{
    private  $conn;

We start by including the file with the database connection parameters and by declaring the class and the conn property which represents the connection resource.

Now we write the method for connecting to the database on which there is little to say:

private function dbConnect()
{
    $this->conn = mysql_connect(DB_HOST,DB_USERNAME,DB_PASSWORD) OR  die();
    mysql_select_db(DB_NAME,$this->conn) OR die();
}

And we declare this method in the class constructor given that any operation that this class has to perform needs connection to the database.

public function __construct()
{
    $this->dbConnect();
}

Now we can develop the method thanks to which we will display the item list and which is going to substitute the static list in the index.php page:

public function showItem()
{
    $sql = "SELECT * FROM lista ORDER BY item_order";
    $res = mysql_query($sql, $this->conn);

    while($row = mysql_fetch_array($res))
    {
        echo '<li id="item_' . $row['id'] . '">' .  $row['item_name'] . '</li>';
    }
    return;
}

As you can see the data are extracted sorted in accordance with the item_order field;  next we will create the list in a dynamic way. The id of the item will be: item_valueofIdExtractedfromDatabase. All we have to do now is instantiate the class. At this point our script should be like this (it’s not finished yet):

include_once  'db_config.php';

class  sortableSupport
{
    private  $conn;

        public function __construct()
        {
            $this->dbConnect();
        }

        private function dbConnect()
        {
            $this->conn = mysql_connect(DB_HOST,DB_USERNAME,DB_PASSWORD) OR  die();
            mysql_select_db(DB_NAME,$this->conn) OR die();
        }

        public function showItem()
        {
            $sql = "SELECT * FROM lista ORDER BY item_order";
            $res = mysql_query($sql, $this->conn);

            while($row = mysql_fetch_array($res))
            {
                echo '<li id="item_' . $row['id'] . '">' .  $row['item_name'] . '</li>';
            }

            return;
        }
}

$sortableSupport  = new sortableSupport();

Now we open the index.php file we used for the previous article. In this file we have to substitute the item list with the showItem() method.

Thus this part of the code:

<ul  id="list">
    <li id="item_1">Item 1</li>
    <li id="item_2">Item 2</li>
    <li id="item_3">Item 3</li>
    <li id="item_4">Item 4</li>
    <li id="item_5">Item 5</li>
</ul>

It will be substituted with this:

<ul  id="list">
    <?php
    include_once 'sortableSupport.php';
    $sortableSupport->showItem();
    ?>
</ul>

Like this our list will be dynamically inserted.

Detecting the change of order

Now we are at a crucial passage. We have to somehow detect the change of order and send it to the server so that it’s possible to update the database.
We start by getting back to our jQuery script we left like this:

$("#list").sortable({
    opacity:0.5,
    axis: "y"
    });

At this point we will pass as parameter the administrator of update event through which we define the procedures to perform when the list is modified. As always we will achieve this by means of a function, just like this:

$("#list").sortable({
    opacity:0.5,
    axis: "y",
    update:  function(event,ui){
        // what to do when the list is modified
        }
    });

Through this function we should read the order of the list and send it to the server by means of an ajax request.
In order to read the order of elements all we have to do is to serialize the list.
Well, even sortable has a serialize method we will simply use it this way:

var  itemOrder = $('#list').sortable('serialize');

In the previous article I insisted on the importance of the format of the id of the list of items: nameAlwaysSame_numberAlwaysDifferent
Concretely

<li  class="item" id="item_1">Elemento 1</li>

The reason behind this particular format is the following:
Serialize expects this format and will restore an array whose name will be what’s in front of the hyphen, while the values will be those after the hyphen.
The values of this array will be sorted in the same way in which the list is sorted.
Thus if we move item 2 before item 1, the resulting array will be this

2,1,3,4,5

Or more precisely, if we keep into account the keys also:

item[0] -> 2
item[1] -> 1
item[2] -> 3
item[3] -> 4
item[4] -> 5

All that remains to be done is to send this array to the server through a simple ajax request:

$.post("order.php",  itemOrder);

Obviously we still have to write order.php, but we will do that soon.
Our jQuery code is ready, rather the entire index.php page is ready and looks like this:

<!DOCTYPE  HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"  "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
    <meta  http-equiv="content-type" content="text/html; charset=utf-8">
    <script  type="text/javascript"  src="jquery/jquery-1.4.2.js"></script>
    <script  type="text/javascript"  src="jquery/ui/jquery.ui.core.js"></script>
    <script  type="text/javascript"  src="jquery/ui/jquery.ui.widget.js"></script>
    <script  type="text/javascript"  src="jquery/ui/jquery.ui.mouse.js"></script>
    <script  type="text/javascript"  src="jquery/ui/jquery.ui.sortable.js"></script>
    <link  href="screen.css" rel="stylesheet" type="text/css" />
    <script  type="text/javascript">
        $(document).ready(function(){
            $("#list").sortable({
                opacity:0.5,
                axis: "y",
                update: function(event,ui){
                    var itemOrder = $('#list').sortable('serialize');
                    $.post("order.php", itemOrder);
                    }
                });
            });
    </script>
    <title>Sorting with jQuery | Your Inspiration Web</title>
</head>
<body>
<div  id="container">
    <ul  id="list">
        <?php
        include_once 'sortableSupport.php';
        $sortableSupport->showItem();
        ?>
    </ul>
</div>
</body>
</html>

How to update the sorting of the database?

Now our array is on its way to the server. We start developing a new method of the  sortableSupport class which is capable of updating the sorting of the database.

In order to achieve this we will loop through the array with the foreach construct thanks to which we will draw the key for each element (which will represent the order) and the value that will correspond to the item id. So for each element we will have the id and sorting number. All we have to do is update the table, like this:

public function orderItem()
{
    foreach($_POST['item'] as $order => $id)
    {
        $sql = "UPDATE lista SET item_order=$order WHERE  id=$id";
        mysql_query($sql, $this->conn);
    }
}

As you can see we modify the item_order field in correspondence with the id.

All that remains to be done at this point is creating the order.php file, in which we have to uniquely execute the method we just saw.

include_once  'sortableSupport.php';
$sortableSupport->orderItem();

To finish I bring you back the entire code of sortableSupport class:

include_once  'db_config.php';

class  sortableSupport
{
    private  $conn;

        public function __construct()
        {
            $this->dbConnect();
        }

        private function dbConnect()
        {
            $this->conn = mysql_connect(DB_HOST,DB_USERNAME,DB_PASSWORD) OR  die();
            mysql_select_db(DB_NAME,$this->conn) OR die();
        }

        public function showItem()
        {
            $sql = "SELECT * FROM lista ORDER BY item_order";
            $res = mysql_query($sql, $this->conn);

            while($row = mysql_fetch_array($res))
            {
                echo '<li id="item_' . $row['id'] . '">' .  $row['item_name'] . '</li>';
            }

            return;
        }

        public function orderItem()
        {
            foreach($_POST['item'] as $order => $id)
            {
                $sql = "UPDATE lista SET item_order=$order WHERE  id=$id";
                mysql_query($sql, $this->conn);
            }
        }
}

$sortableSupport  = new sortableSupport();

Conclusion

The article came out a little bit longer but I wanted to explain everything into details. As you noticed sortable provides all supports for being able to implement the sorting of objects in a simple way.
And you, have you already used such feature? In what occasion?

Master per Web Designer Freelance
In tutti questi anni abbiamo ricevuto centinaia di richieste di approfondimento sulle numerose tematiche del web design vissuto da freelance. Le abbiamo affrontate volta per volta. Ma ci siamo resi conto che era necessario fare qualcosa di più. Ecco perché è nato One Year Together, un vero e proprio master per web designer freelance che apre finalmente le porte al mondo del lavoro.
Scopri One Year Together »
[pdf]Scarica articolo in PDF[/pdf]
Tags: , ,

The Author

Maurizio is married to the triad PHP - MySql - Apache and, not enough, he has a lover called jQuery. He has a blog where he tries to describe in detail all of "his lovers". His real specialty is the realization of large business application, altough he never refuses the commitment of a website.

Author's web site | Other articles written by

Related Posts

You may be interested in the following articles:

8 comments

  1. Jack

Trackback e pingback

  1. How to order the elements with jQuery (2/2)
    [...] How to order the elements with jQuery (2/2) [...]
  2. Element with jquery |Avnish Namdev
    [...] In the previous article we saw how the sortable method allows the sorting of a list of items through …

Leave a Reply

Current day month ye@r *