Paginating Data with CakePHP and Yahoo! User Interface DataTable

The aim of this tutorial is to be able to pull data from your database and output it in a nice shiny data table. Although the cakePHP paginator does this perfectly well, I have been using YUI and I am not a fan of the prototype library.

The YUI Paginator control is able to send requests to the server with parameters for the page required, the DataTable control can then format that data into our shiny table.

For this tutorial I have used CakePHP 1.2 RC3 and YUI 2.6.0. I am using a “Products” controller, model and corresponding views.

Thanks should go to this post http://www.ntatd.org/mark/?p=32 by Mark Buckner (aka hydra12) describing how to use cakePHP with ExtJS DataGrid, for the inspiration and the some of the cakePHP code for this tutorial.

The first step is to extract the data and return it to the view. To do this we need to create a new action in the controller.

function results($page=0,$limit=10) {
$this->layout = "ajax";  //make cake use the ajax layout
$productArray = array();  //this will hold our data from the database.
$count = $this->Product->findCount(); //counts the number of records in Message.
$productA = $this->Product->find('all', array('limit'=>$limit,'page'=>$page,'order' => 'Product.created DESC')); //gets all the Product records and sorts them by date created.
$productArray = Set::extract($productA, '{n}.Product');  //convert $productArray into a json-friendly format
$this->set('total', $count);  //send total to the view
$this->set('page_size',$limit);
$this->set('product_list',$productArray);  //send messages to the view
}

Next, create a view file called results.ctp in /views/products/. In this file we will set out the data given to us by the controller in to JSON format. YUI DataTable will be able to use this to input the data into our shiny table later.
<?php
 echo '{"totalRecords":'.$total.',
        "recordsReturned":'.$page_size.',
        "records":'.$javascript->Object($product_list).'}';
?>

Now if all is well and you go to http://www.example.com/products/results, you should be presented with your JSON array.

To keep things simple I have put the javascript needed in a seperate file, products.js. This will be imported into the view with the javascript helper.

YAHOO.example.DynamicData = function() {
  // Column definitions
  var myColumnDefs = [
      {key:"id", label:"Id"},
      {key:"code", label:"Code"},
      {key:"name", label:"Name"},
      {key:"quantity", label:"Qty"}
  ];

  // DataSource instance
  var myDataSource = new YAHOO.util.DataSource("http://www.example.com/products/results/");
 // This is where the DataTable gets its data from.
 myDataSource.responseType = YAHOO.util.DataSource.TYPE_JSON;
 myDataSource.responseSchema = {
     resultsList: "records",
     fields: [
       {key:"id"},
       {key:"code",parser:"number"},
       {key:"name"},
       {key:"quantity",parser:"number"}
     ],
     metaFields: {
         totalRecords: "totalRecords" // Access to value in the server response
     }
 };

// This tells the DataTable how to pass variables to the URL, for this example all we need is the current page.
 var myRequestBuilder = function(oState, oSelf) {
 // Get states or use defaults
 oState = oState || {pagination:null};
 var page = oState.pagination.page;
 // Build custom request
 return  page;
 );

 // DataTable configuration
 var myConfigs = {
     initialRequest: "1", // Initial request for first page of data
     dynamicData: true, // Enables dynamic server-driven data
     paginator: new YAHOO.widget.Paginator({ rowsPerPage:10 }),
     generateRequest : myRequestBuilder}; // Enables pagination

 // DataTable instance
 var myDataTable = new YAHOO.widget.DataTable("dynamicdata", myColumnDefs, myDataSource, myConfigs);
 myDataTable.hideColumn("id");//this hides the id column, so it is not visible but we can use the data at a later date.

// Update totalRecords on the fly with value from server
 myDataTable.handleDataReturnPayload = function(oRequest, oResponse, oPayload) {
     oPayload.totalRecords = oResponse.meta.totalRecords;
     return oPayload;
 }

 return {
     ds: myDataSource,
     dt: myDataTable
 };

}();

Now, all we need to add to the view of the page that we want the DataTable to appear on is this,
<div id="dynamicdata"></div>
<?php
echo $javascript->includeScript('products');
//this is our js file with all the YUI goodness
?>

In the layout file we need to add the YUI files between the <head> tags,

<!-- Combo-handled YUI CSS files: -->
<link rel="stylesheet" type="text/css" href="http://www.duncanbrown.me.uk/wp-content/uploads/2008/10/datatable.css">
<!-- Combo-handled YUI JS files: -->
<script type="text/javascript" src="http://yui.yahooapis.com/combo?2.6.0/build/yahoo-dom-event/yahoo-dom-event.js&2.6.0/build/connection/connection-min.js&2.6.0/build/datasource/datasource-min.js&2.6.0/build/element/element-beta-min.js&2.6.0/build/datatable/datatable-min.js"></script>

Of course you can use the html and javascript helpers for this if you want. You can see how the file requests are configured here, http://developer.yahoo.com/yui/articles/hosting/?connection&datasource&datatable&MIN. To use the built-in “skin” you need to give your <body> a class of yui-skin-sam, so <body class=”yui-skin-sam”>. The skin can easily be modified as explained here, http://developer.yahoo.com/yui/articles/skinning/.

Now when you visit the page you have put the DataTable on to, you should see your nice shiny table and your data retrieved, also YUI puts this navigation above and below the table, which can also be customised with skins.

YUI DataTable Paginator control.
YUI DataTable Paginator control.

Happy Baking and I hope this was useful.

14 thoughts on “Paginating Data with CakePHP and Yahoo! User Interface DataTable

  • October 24, 2008 at 12:02 pm
    Permalink

    Very cool tutorial, and i hope you write more of them 🙂 the cakePHP community can always use more cool tutorials and blog posts.

    Reply
  • February 28, 2009 at 9:42 pm
    Permalink

    hi duncan,

    Great tutor and I tried it but looks doesn’t work properly. Below are my code:

    units_controller
    function results($page=0,$limit=10) {
    $this->layout = “ajax”;
    $UnitArray = array(); //this will hold our data from the database.
    $count = $this->Unit->findCount(); //counts the number of records in Message.
    $UnitA = $this->Unit->find(‘all’, array(‘limit’=>$limit,’page’=>$page,’order’ => ‘Unit.created DESC’)); //gets all the Product records and sorts them by date created.
    $UnitArray = Set::extract($UnitA, ‘{n}.Unit’); //convert $productArray into a json-friendly format
    $this->set(‘total’, $count); //send total to the view
    $this->set(‘page_size’,$limit);
    $this->set(‘unit_list’,$UnitArray); //send messages to the view
    }

    results.ctp

    Object($unit_list).’}’;
    ?>

    and i also have created unit.js and link it from my default.ctp like echo $javascript->link(‘unit’);

    the rest is same in this tutorial.
    But the result is just display data with no style like below :

    {“totalRecords”:725, “recordsReturned”:10, “records”:[{“id”:”1″,”name”:”TA/05/AA”,”building_id”:”1″,”unit_type_id”:”1″,”block_id”:”0″,”floor”:”5″,”number”:”AA”,”size”:”90,27″,”#kwh_meter”:”1″,”created_by”:”Admin”,”created”:”0000-00-00 , and on and on…until 10 records

    what’s wrong? please let me now.

    really thank

    Reply
  • February 28, 2009 at 11:20 pm
    Permalink

    Hi hermawan,
    What is the url that is showing this, it sounds like it is http://www.example.com/units/results.
    If it is then that is right, because it is that url YUI looks at to get the data to put in the view. Your “shiny” datatable would be on http://www.example.com/units. You need to put the div with id of “dynamicdata” and link the unit.js file in your index view file (index.ctp).

    Hope this helps.

    Reply
  • March 2, 2009 at 9:13 pm
    Permalink

    Hi Duncan,

    thank 4 your response. Sure the url is http://localhost/myapp/units/results and in the index.ctp i put and link to unit.js. The stylesheet (css) and other javascript call directly to yui sites, from my default layout beetwen
    then, i run http://localhost/myapp/units and still same. nothing change, The pagination is still cakephp standard.
    any suggest? or i was wrong ?
    thk

    Reply
  • March 2, 2009 at 9:19 pm
    Permalink

    missing word and correction

    index.ctp

    link(‘unit.js’);?> //unit.js in webrootcss

    default.ctp:

    link to css & other javascript

    Reply
  • March 11, 2009 at 9:43 pm
    Permalink

    missing word and correction

    index.ctp

    link(’unit.js’);?> //unit.js in webrootcss

    If you use

    echo $javascript->includeScript(‘units’);

    cake will add a script tag with src of /js/units.js

    Reply
  • May 7, 2009 at 6:11 pm
    Permalink

    just add this to the template and it’ll work 😉

    Required for the Paginator 🙂

    Reply
  • May 29, 2009 at 12:27 am
    Permalink

    Duncan thanks for this documentation, but can you republish again with the correction, I can not making work yet

    Reply
  • May 29, 2009 at 5:57 am
    Permalink

    I try many times and I can not make it work, can you upload the sample files to download please?

    Reply
  • September 23, 2009 at 1:12 pm
    Permalink

    Hi,

    Great Tutorial! I just have a question about the url you use in your example.

    The one u use is: http://www.example.com/units/results. Now I am just wondering if YUI doesn’t accept these type of urls. It prefers: http://www.example.com/units/results.php instead.

    I tried doing this datatable and couldn’t get the data because of the url. I also tied doing the autocomplete same issue. Just wondering if anyone came across this situation.

    thanks

    Reply
  • September 24, 2009 at 7:30 pm
    Permalink

    @Jerry: Hi, I think I have found the problem, if you are following the tutorial you don’t need {“ResultSet”: at the beginning (and the last “}”).

    If this doesn’t help can you give a link to the page the datatable is on?

    Reply

Leave a Reply

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