Paginating, Sorting and Displaying Data with CakePHP

Updated last July 15, 2013: CakePHP 2.x Pagination Tutorial: Helper, Conditions, Limit, Sorting and More!

Today I’m going to show you how easy it is for CakePHP to do pagination, sorting and displaying data from the database (I’m using MySQL). Pagination is useful if you have many rows of data, image if you have thousands of records, your page would load slow and it will be very inconvenient for your users to browse that page. Sorting is useful if you want to view your data in alphabetical, ascending or descending order.

We will have our table look like this:

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(32) NOT NULL,
  `lastname` varchar(32) NOT NULL,
  `email` varchar(32) NOT NULL,
  `username` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
)

So in our app/models/user.php we will have something like this:

<?php
class User extends AppModel{
    var $name = ‘User’;
?>

A function on our app/controllers/users_controller.php:

    //for this example, we display two records per page
    $this->paginate = array(
        ‘limit’ => 2
    );

    //we will set it to users variable for the view
    $this->set(‘users’, $this->paginate(‘User’));
}

Our view file on app/views/users/view_users.ctp:

<?php
echo “<div class=’page-title’>Users</div>”; //title
//this ‘add new user’ button will be used for the next tutorial
echo “<div style=’float:right;’>”;
    $url = “add/”;
    echo $form->button(‘Add New User’, array(‘onclick’ => “location.href=’”.$this->Html->url($url).“‘”));
echo “</div>”;
echo “<div style=’clear:both;’></div>”;

if( sizeOf( $users ) > 0 ){ //check if there are user records returned
?>
<table>
    <tr>
        <!–
            Here on the table heading (<th></th>) is where our SORTING occurs,
            User has to click heading label to sort data in ascending or descending order,
            $paginator->sort(‘Firstname’, ‘firstname’); is a CakePHP function that builds the link for sorting
            the first parameter ‘Firstname’ will be the label
            and the second parameter ‘firstname’ is actually the database field
        –>
        <th style=’text-align: left;’><?php echo $paginator->sort(‘Firstname’, ‘firstname’); ?></th>
        <th><?php echo $paginator->sort(‘Lastname’, ‘lastname’); ?></th>
        <th><?php echo $paginator->sort(‘Email’, ‘email’); ?></th>
        <th><?php echo $paginator->sort(‘Username’, ‘username’); ?></th>
        <th>Action</th>
    </tr>
    <tr>
    <?php
        foreach( $users as $user ){ //we wil loop through the records to DISPLAY DATA
            echo “<tr>”;
                echo “<td>”;
                    //$user is our foreach variable
                    //['User'] is from our model/alias
                    //['firstname'] is the database field
                    echo “{$user['User']['firstname']}“;
                echo “</td>”;
                echo “<td>{$user['User']['lastname']}</td>”;
                echo “<td>{$user['User']['email']}</td>”;
                echo “<td>{$user['User']['username']}</td>”;
                echo “<td style=’text-align: center;’>”;
                    //’Edit’ and ‘Delete’ link here will be used for our next tutorials
                    echo $html->link(‘Edit’, array(‘action’=>‘edit/’.$user['User']['id']), null, null);
                    echo ” / “;
                    echo $html->link(‘Delete’, array(‘action’=>‘delete/’.$user['User']['id']), null, ‘Are you sure you want to delete this record?’);
                echo “</td>”;
            echo “</tr>”;
        }
    ?>
    </tr>
</table>

<?php
    //here is our PAGINATION part
    echo “<div class=’paging’>”;

    //for the first page link
    //the parameter ‘First’ is the label, same with other pagination links
    echo $paginator->first(‘First’);
    echo ” “;

    //if there are previous records, prev link will be displayed
    if($paginator->hasPrev()){
        echo $paginator->prev(‘<<’);
    }

    echo ” “;
    //modulus => 2 specifies how many page numbers will be displayed
    echo $paginator->numbers(array(‘modulus’ => 2));
    echo ” “;

    //there are next records, next link will be displayed
    if($paginator->hasNext()){
        echo $paginator->next(‘>>’);
    }

    echo ” “;
    //for the last page link
    echo $paginator->last(‘Last’);

    echo “</div>”;

}else{ //if there are no records found, display this
    echo “<div class=’no-records-found’>No Users found.</div>”;
}

?>

Our output will look like this:

Data is displayed, you can click table heading link for sorting and page links are on the bottom part of the page. That’s it!