CakePHP 2.0 AJAX sortable list that updates to databases

It is easy to create an AJAX sortable list with CakePHP 2.0, using jQuery that automatically updates the database, however it is hard to find documentation explaining how to do it.

Here is my explanation – it assumes a basic knowledge of CakePHP.

Step 1: Data

Lets use a simple categories table which includes a column for sort_order. This will be used to define the order the records are displayed on the page.

CREATE TABLE `categories` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(60) DEFAULT NULL,
  `sort_order` INT(10) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Step 2: CakePHP

Bake a model, controller and views for this table so that we have some files to work with.
You should be able to go onto your website at http://yoururl/categories and see the default index view.

Layout

In order to use the sortable feature, you will need to edit the default layout. If you don’t already have your own layout you can copy the CakePHP default one (which is used if your own one isn’t found) from /lib/Cake/View/Layouts/default.ctp and place it in app/View/Layouts/default.ctp.
Edit this so that the writeBuffer method is one of the final lines, just before the close body tag:

<?php echo $this->Js->writeBuffer();?>
</body>
</html>

This will create the HTML and JavaScript generated by the Js helper in the view.

Controller

At the top of the CategoriesController make the Js helper available to the controller.

public $helpers = array('Js');

I am going to add 2 actions, one to display the sorted categories and one to handle the AJAX requests to update the database.
The first action is called sort, all this does is retrieve all the categories sorted by the sort_order column and send them off to the view.

public function sort() {
	$this->set('categories', $this->Category->find('all',array(
		'order' => 'sort_order ASC',
		)
	));
}

The second action is called reorder.
This will receive the AJAX post and update the categories table. I’ve commented out a line that can be used for debugging. If uncommented this will write to app/tmp/logs/error.log and is a really good way to see what is happening when you are dealing with AJAX requests.

public function reorder() {
	foreach ($this->data['Category'] as $key => $value) {
		$this->Category->id = $value;
		$this->Category->saveField("sort_order",$key + 1);
	}
	//$this->log(print_r($this->data,true));
	exit();
}

The code here is just looping through the posted data which will look like this:

Array
(
    [Category] => Array
        (
            [0] => 4
            [1] => 5
            [2] => 3
            [3] => 6
            [4] => 2
            [5] => 1
        )
)

The index is the order of elements after they have been sorted and the value is the id of each element. When saving the data I am adding 1 to the index as the one coming from jQuery is zero based.

View

For the sake of simplicity I’ve included the links to the jQuery and jQuery UI libraries here but in practice you would probably want to put them in the head of your layout.
In the view I have a simple unordered list tag containing the data.
The id of each element is important as this is used by jQuery UI to format the data sent back to the server.

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.6.4/jquery.min.js"></script>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.16/jquery-ui.min.js"></script>
<ul id="my-list">
	<?php foreach ($categories as $category): ?>
	<li id='Category_<?php echo $category['Category']['id']?>'><?php echo $category['Category']['name']; ?></li>
	<?php endforeach; ?>
</ul>
<?php
$this->Js->get('#my-list');
$this->Js->sortable(array(
	'complete' => '$.post("/categories/reorder", $("#my-list").sortable("serialize"))',
	));
?>

And that is all there is to it.
You can simply drag and drop any item in the list and behind the scenes the database will be updated with the new sort order.

This entry was posted in MySQL, PHP. Bookmark the permalink.

8 Responses to CakePHP 2.0 AJAX sortable list that updates to databases

  1. Tihanyi Laszlo says:

    Hi, great tutorial, almost works for me. Only problem is the posted parameters in the ajax request(I suggest).
    copied from the firebug console:

    Parameterek
    application/x-www-form-urlencoded
    Category[] 1
    Category[] 3
    Category[] 2
    Forras
    Category[]=1&Category[]=3&Category[]=2

    How can i solve this?
    Or what could be the problem, if it is not this.

    Thanks

    • Richard says:

      Hi – Yes, that’s absolutely right.
      I’ve just checked my version with Firebug and it is posting the following to the controller:
      Category[]=2&Category[]=4&Category[]=1&Category[]=6&Category[]=5&Category[]=3

      However within the controller $this->data contains:

      Array
      (
          [Category] => Array
              (
                  [0] => 2
                  [1] => 4
                  [2] => 1
                  [3] => 6
                  [4] => 5
                  [5] => 3
              )
      )
      

      Which is enough to save the new order back to the Model.

  2. Tihanyi Laszlo says:

    Ooops, it really works now! Thanks! I probably missed something yesterday! But there was surely an error in the ajax response yesterday, as i remember but i missed to copy here but it doesn’t matter now probably it wasn’t connected to your code. Anyway that is a great tutorial for me surely! Have a nice day!

  3. David says:

    Thanks for the awesome tutorial Richard.

    This helped me sort my lists in 1.3 with only a small change to the reorder action. I also found Touch Punch very handy for making sortable and other jQuery stuff work on a handheld.

  4. Josh says:

    Awesome! Been looking for this for a while
    I had to change the relative URL to absolute in the post to get it to work, but it’s all still in dev locally.
    THANKS!

  5. Tony says:

    Nice tutorial. I have this working with a table but the only issue I’m have is that as I’m dragging a table row, the row shrinks. I found a fix but I’m not sure how to implement it via cakephp: http://jsfiddle.net/bgrins/tzYbU/ . Any Suggestions?

    Thanks

  6. Dobri Dobrev says:

    This is very nice! I like it!
    But what if we got more than one list and the ability to sort between lists like here:
    http://interface.eyecon.ro/demos/sort.html

  7. Brandon says:

    thanks for the tutorial!

    one small modification that I had to make was calling the sort() function in the index() function of my app’s controller file. without doing so, the items would not sort properly when being viewed.

    $this->sort();

Leave a Reply

Your email address will not be published. Required fields are marked *


9 × = 45

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>