Zend Framework 2 Pagination & Sorting Tutorial

The purpose of this tutorial is to demonstrate pagination features of the Zend Framework . As an added bonus I will show you how to combine pagination and data sorting to display sorted tables in your ZF2 web projects. For the purpose of this tutorial we will extend Rob Allen’s famous Album Inventory Tutorial. You can download the complete source code from Github.

Download Source

Before getting started you will need to have completed the Album’s tutorial or you can download the source code from Github or clone the repository (instructions in the Installation section).

Downloading the Source Code

1. Download Source and extract it to your project’s directory

2. Git Clone Methods:

Here you have 3 choices and since we only need a read-only copy of the file it makes no difference on which method you use. You can clone the repository by any of the following commands. Open a terminal and run on of following command.

git clone https://github.com/akrabat/zf2-tutorial.git // HTTP
git clone git@github.com:akrabat/zf2-tutorial.git // SSH
git clone git://github.com/akrabat/zf2-tutorial.git // GIT Read-Only

Installation

To install the application using composer run the following command

php composer.phar self-update // always use the latest composer
php composer.phar install // install dependencies

To test your installation you should add a virtual host that points to the public folder of your project’s directory. Visit `Adding a Virtual Host` section of the StickyNotes Tutorial for more information on this topic.

Even though, we have installed the dependencies and created a virtual host we still need to configure our database and create appropriate database tables.
The Albums project uses MySQL via PHP’s PDO driver. As per instructions from the Albums Tutorial create a database called zf2tutorial, and run the following SQL statements to create the album table and add some test data to it.

CREATE TABLE album (
  id int(11) NOT NULL auto_increment,
  artist varchar(100) NOT NULL,
  title varchar(100) NOT NULL,
  PRIMARY KEY (id)
);
INSERT INTO album (artist, title)
    VALUES  ('The  Military  Wives',  'In  My  Dreams');
INSERT INTO album (artist, title)
    VALUES  ('Adele',  '21');
INSERT INTO album (artist, title)
    VALUES  ('Bruce  Springsteen',  'Wrecking Ball (Deluxe)');
INSERT INTO album (artist, title)
    VALUES  ('Lana  Del  Rey',  'Born  To  Die');
INSERT INTO album (artist, title)
    VALUES  ('Gotye',  'Making  Mirrors');

You should also tell your application how to access the database by modifying the global configuration file. Navigate to `config/autoload/global.php` and enter your database credentials in that file.

return array(
    'db' => array(
        'driver' => 'Pdo',
        'dsn' => 'mysql:dbname=zf2tutorial;hostname=localhost',
        'username' => 'YOUR_USERNAME', 
        'password' => 'YOUR_PASSWORD',
        'driver_options' => array(
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''
        ),
    ),
    'service_manager' => array(
        'factories' => array(
            'Zend\Db\Adapter\Adapter' => 'Zend\Db\Adapter\AdapterServiceFactory',
        ),
    ),
);

However, it is not recommended to put your database’s username and password in the global configuration file as you might have many installations on different servers for development, staging and production you can read more on that in our StickyNotes Tutorial under Adding a Virtual Host and to learn how to configure your local settings head to Database and Models section of StickyNotes Tutorial.

Finally the last step is optional. The glyphicons that ship with the Bootstrap are not included in ZF2 by default. The quickest way to enable these is to download Bootsrap and place the `img` folder in the public folder of your project.

Sorting

Since we only have two data columns in our database (`title` & `artist`) we will modify the existing code to enable sorting these two columns. However, when dealing with real life projects you might have many columns and would have to decide which columns you want to make sortable. We will modify the `fetchAll` function in the `AlbumTable` Class to enable passing a `Zend\Db\Sql\Select` object to create sophisticated queries.

// /module/Album/src/Model/AlbumTable.php
use Zend\Db\Sql\Select;
...
    public function fetchAll(Select $select = null) {
        if (null === $select)
            $select = new Select();
        $select->from($this->table);
        $resultSet = $this->selectWith($select);
        $resultSet->buffer();
        return $resultSet;
    }
...

If the $select variable is null the function will fetch all the rows in our album table. Otherwise, the function will query results based on the criteria set in our Select object. The returned ResultSet object is an iterator and based on its implementation it disables `buffer` by default. Meaning the returned list can only be traversed once. However, we might need to do multiple operations on this ResultSet e.g. listing the results in a table and displaying pagination links. Hence, we call the buffer method of the AbstractResultSet class to enable buffering.

Router

We will modify module/config/module.config.php to allow passing order_by (column title) and order (ASC, DESC) to our controller.

'options' => array(
                    'route'    => '/album[/:action][/:id][/order_by/:order_by][/:order]',
                    'constraints' => array(
                        'action' => '(?!\border_by\b)[a-zA-Z][a-zA-Z0-9_-]*',
                        'id'     => '[0-9]+',
                        'order_by' => '[a-zA-Z][a-zA-Z0-9_-]*',
                        'order' => 'ASC|DESC',
                    ), 

by prepending “(?!\border_by\b)” to action we exclude `order_by` from the action list and hence we cannot create an `order_byAction` in our Controller. This enables the application to correctly point to the default URL and sort the data without specifying the index action. We only alow ASC or DESC values for order.

Controller

Modify the indexAction in the AlbumController.

public function indexAction() {
        $select = new Select();

        $order_by = $this->params()->fromRoute('order_by') ?
                $this->params()->fromRoute('order_by') : 'id';
        $order = $this->params()->fromRoute('order') ?
                $this->params()->fromRoute('order') : Select::ORDER_ASCENDING;

        $select->order($order_by . ' ' . $order);

        return new ViewModel(array(
                    'albums' => $this->getAlbumTable()->fetchAll($select),
                    'order_by' => $order_by,
                    'order' => $order,
                ));
    }

 View

The final step here is to change the table header (Title & Artist) and using the `order_by` and `order` variables create the correct links to achieve sorting.

// module/view/album/album/index.phtml

<?php
$title = 'My albums';
$this->headTitle($title);
$url_order = 'ASC';  // the following lines
if ($order_by == 'title')
    $url_order = $order == 'ASC' ? 'DESC' : 'ASC';
elseif ($order_by == 'artist')
    $url_order = $order == 'ASC' ? 'DESC' : 'ASC';
?>
…
<table class="table">
    <tr>
        <th>
            <a href="<?php echo $this->url('album', array('order_by' => 'title', 'order' => $url_order)); ?>">Title <?php if ($order_by == 'title'): ?><i class="icon-chevron-<?php echo $url_order == 'ASC' ? 'down' : 'up' ?>"></i><?php endif; ?></a>
        </th>
        <th>
            <a href="<?php echo $this->url('album', array('order_by' => 'artist', 'order' => $url_order)); ?>">Artist <?php if ($order_by == 'artist'): ?><i class="icon-chevron-<?php echo $url_order == 'ASC' ? 'down' : 'up' ?>"></i><?php endif; ?></a></th>
        <th>&nbsp;</th>
    </tr>
…

Pagination

Setting up pagination is a very simple task. We will use the Zend\Paginaror\Paginator class to handle the pagination.

Router

We will modify module/config/module.config.php to allow the pagination data i.e. page number.

options' => array(
                    'route'    => '/album[/:action][/:id][/page/:page][/order_by/:order_by][/:order]',
                    'constraints' => array(
                        'action' => '(?!\bpage\b)(?!\border_by\b)[a-zA-Z][a-zA-Z0-9_-]*',
                        'id'     => '[0-9]+',
                        'page' => '[0-9]+',
                        'order_by' => '[a-zA-Z][a-zA-Z0-9_-]*',
                        'order' => 'ASC|DESC',
                    ),
                    'defaults' => array(
                        'controller' => 'Album\Controller\Album',
                        'action'     => 'index',
                    ),
                ),

Controller

In the `indexAction` of the `AlbumController` we will get the page number from URL and create the appropriate `Paginator` object.

use Zend\Paginator\Paginator;
use Zend\Paginator\Adapter\Iterator as paginatorIterator;
...
public function indexAction() {
        $select = new Select();

        $order_by = $this->params()->fromRoute('order_by') ?
                $this->params()->fromRoute('order_by') : 'id';
        $order = $this->params()->fromRoute('order') ?
                $this->params()->fromRoute('order') : Select::ORDER_ASCENDING;
        $page = $this->params()->fromRoute('page') ? (int) $this->params()->fromRoute('page') : 1;

        $albums = $this->getAlbumTable()->fetchAll($select->order($order_by . ' ' . $order));
        $itemsPerPage = 2;

        $albums->current();
        $paginator = new Paginator(new paginatorIterator($albums));
        $paginator->setCurrentPageNumber($page)
                ->setItemCountPerPage($itemsPerPage)
                ->setPageRange(7);

        return new ViewModel(array(
                    'order_by' => $order_by,
                    'order' => $order,
                    'page' => $page,
                    'paginator' => $paginator,
                ));
    }

The `$page` variable holds the current page number. `$itemsPerPage` defines the number of objects (Albums) that we want to display in our table. The collection passed to the `Paginator` class must be of type `Paginator\Adapter`. Here we will use the Iterator Adapter since the `ResultSet` returned from the `fetchAll` function is of type Iterator. Next we setup the options for our `Paginator` object. The `pageRange` option specifies the range of pages from the current page the paginator will display in our pagination links.

View Template

Create a folder if it does not exist under module/Album/view called layout and add the following file. Here we use the styling provided by the Bootstrap.

// module/Album/view/layout/slidePaginator.phtml
<?php if ($this->pageCount): ?>
    <div class="pagination pagination-centered">
        <ul>
            <li <?php echo!isset($this->previous) ? 'class="disabled"' : ''; ?>>
                <a href="<?php echo $this->url('album', array('page' => $this->first, 'order_by' => $order_by, 'order' => $order,)); ?>">&laquo;</a></li>
            <li <?php echo!isset($this->previous) ? 'class="disabled"' : ''; ?>>
                <a href="<?php echo $this->url('album', array('page' => $this->previous, 'order_by' => $order_by, 'order' => $order,)); ?>">&lsaquo;</a></li>

            <!-- Numbered page links -->
            <?php foreach ($this->pagesInRange as $page): ?>
                <li <?php echo $page == $this->current ? 'class="active"' : ''; ?>><a href="<?php echo $this->url('album', array('page' => $page, 'order_by' => $order_by, 'order' => $order,)); ?>">
                        <?php echo $page; ?>
                    </a></li>
            <?php endforeach; ?>

            <!-- Next page link -->
            <li <?php echo!isset($this->next) ? 'class="disabled"' : ''; ?>>
                <a href="<?php echo $this->url('album', array('page' => $this->next, 'order_by' => $order_by, 'order' => $order,)); ?>">&rsaquo;</a></li>
            <!-- Last page link -->
            <li <?php echo!isset($this->next) ? 'class="disabled"' : ''; ?>>
                <a href="<?php echo $this->url('album', array( 'page' => $this->last, 'order_by' => $order_by, 'order' => $order,)); ?>">&raquo;</a></li>
        </ul>
    </div>
<?php endif; ?>

We must add the path to slidePaginator.phtml to our module.config.php file

// module/Album/config/module.config.php
'view_manager' => array(
        ...
        'template_map' => array( 
            'paginator-slide' => __DIR__ . '/../view/layout/slidePaginator.phtml',
        ),
    ),

View

In the view (index.phtml) we only need to use the ViewModels PaginationControl method to show the pagination links. Add the following code to the end of index.phmtl

...
paginationControl(
            $paginator, 'Sliding', 'paginator-slide', array('order_by' => $order_by, 'order' => $order)
    );
    ?>

We are ready to test the completed Application in a browser. You can download the complete source code from Github and view the completed project’s live demo.

30 thoughts on “Zend Framework 2 Pagination & Sorting Tutorial”

  1. Thanks for the tutorial.
    One question…
    Why the second page shows a record title 21 by Adele again at live demo?

  2. Hi JoNiL,

    That is a great point you bring up and thanks for making me go back and check things out.

    A quick look and printing the $albums ResultSet object returned from the AlbumTable shows that when the ResultSet (Iterator) returns the `currentData` field is not set.

    
    ZendDbResultSetResultSet Object ( [allowedReturnTypes:protected] => Array ( [0] => arrayobject [1] => array ) [arrayObjectPrototype:protected] => AlbumModelAlbum Object ( [id] => [artist] => [title] => [inputFilter:protected] => ) [returnType:protected] => arrayobject [buffer:protected] => Array ( ) [count:protected] => 4 [dataSource:protected] => ZendDbAdapterDriverPdoResult Object ( [statementMode:protected] => forward [resource:protected] => PDOStatement Object ( [queryString] => SELECT `album`.* FROM `album` ORDER BY `id` ASC ) [options:protected] => [currentComplete:protected] => [currentData:protected] => [position:protected] => -1 [generatedValue:protected] => 0 [rowCount:protected] => 4 ) [fieldCount:protected] => 3 [position:protected] => 0 ) 
    

    So calling $albums->current(); moves the iterator one position and sets the `currentData` to the first object in the iterator.

    
            $albums->current();
            $paginator = new Paginator(new paginatorIterator($albums));
    

    As you see the ‘currentData’ field is set.

    
    ZendDbResultSetResultSet Object ( [allowedReturnTypes:protected] => Array ( [0] => arrayobject [1] => array ) [arrayObjectPrototype:protected] => AlbumModelAlbum Object ( [id] => [artist] => [title] => [inputFilter:protected] => ) [returnType:protected] => arrayobject [buffer:protected] => Array ( [0] => Array ( [id] => 1 [artist] => The Military Wives [title] => In My Dreams ) ) [count:protected] => 4 [dataSource:protected] => ZendDbAdapterDriverPdoResult Object ( [statementMode:protected] => forward [resource:protected] => PDOStatement Object ( [queryString] => SELECT `album`.* FROM `album` ORDER BY `id` ASC ) [options:protected] => [currentComplete:protected] => [currentData:protected] => Array ( [id] => 1 [artist] => The Military Wives [title] => In My Dreams ) [position:protected] => -1 [generatedValue:protected] => 0 [rowCount:protected] => 4 ) [fieldCount:protected] => 3 [position:protected] => 0 )
    
  3. hi,

    It is really great. But I found an issue with the pagination. I have 150 records in my tables. I kept 30 items per pages. And when I navigate to second and other pages no records are showing up.

    Can you please help me to fix this.

  4. Nice, but you are fetching all rows from db. You should fetch only rows that you are showing at the moment. With bigger tables and complex queries, this solution is usless.

  5. Hey, thanks for this tutorial !!

    I have a problem with the last part (PaginationControl method)

    paginationControl(
                $paginator, 'Sliding', 'paginator-slide', array('order_by' => $order_by, 'order' => $order)
    );

    when I add the code at the end of index.phtml I get an error like this:
    Call to undefined function paginationControl() in bla...

    how can I solve this problem?

        1. The solution is to caller the view helper using the correct syntax

          
          $this->paginationControl(
          $paginator, ‘Sliding’, ‘paginator-slide’, array(‘order_by’ => $order_by, ‘order’ => $order)
          );
          
  6. One more thing – i need to change Module.php in main directory, from:

    
     array(
                    __DIR__ . '/autoload_classmap.php',
                ),
                'Zend\Loader\StandardAutoloader' => array(
                    'namespaces' => array(
                        __NAMESPACE__ => __DIR__ . '/src/' . __NAMESPACE__,
                    ),
                ),
            );
        }
    
        public function getConfig()
        {
            return include __DIR__ . '/config/module.config.php';
        }
    
    // start step 3 Add this method:
        public function getServiceConfig()
        {
            return array(
                'factories' => array(
                    'Album\Model\AlbumTable' =>  function($sm) {
                        $tableGateway = $sm->get('AlbumTableGateway');
                        $table = new AlbumTable($tableGateway);
                        return $table;
                    },
                    'AlbumTableGateway' => function ($sm) {
                        $dbAdapter = $sm->get('Zend\Db\Adapter\Adapter');
                        $resultSetPrototype = new ResultSet();
                        $resultSetPrototype->setArrayObjectPrototype(new Album());
                        return new TableGateway('album', $dbAdapter, null, $resultSetPrototype);
                    },
                ),
            );
        }
    // stop step 3	
    }
    

    to:

    
     array(
                    __DIR__ . '/autoload_classmap.php',
                ),
                'Zend\Loader\StandardAutoloader' => array(
                    'namespaces' => array(
                        __NAMESPACE__ => __DIR__ . '/src/' . __NAMESPACE__,
                    ),
                ),
            );
        }
        
        public function getServiceConfig()
        {
            return array(
                'factories' => array(
                    'Album\Model\AlbumTable' =>  function($sm) {
                        $dbAdapter = $sm->get('Zend\Db\Adapter\Adapter');
                        $table = new AlbumTable($dbAdapter);
                        return $table;
                    },
                ),
            );
        }    
    
        public function getConfig()
        {
            return include __DIR__ . '/config/module.config.php';
        }
    }
    
  7. This is not printing out any pagination control in view….

    echo $this->paginationControl(
                $paginator, 'Sliding', 'paginator-slide', array('order_by' => $order_by, 'order' => $order)
        );
  8. Nice. But can’t we right without ‘Router’. Because, the site which I am working on is not having this router file till now. Now how I have to add this router file. Can you post simple code rather than this…..

    Thank you…….

    1. I tried to implement the paginatorSlide view script without the `album`specific route following the readthedocs tutorial:

      https://zf2.readthedocs.org/en/latest/tutorials/paginator.control.html?highlight=php

      Unfortunately this one looks buggy since a route is required!

      This post helped:
      http://stackoverflow.com/questions/13048650/zend-framework-2-pagination-routing-issue

      You need to use the fourth parameter of the paginationControl view helper:

      paginationControl($this->paginator, 'Sliding',
                'my_pagination_control', array('route' => 'paginator_route')); 
      ?>

      You can pass through parameters to the view partial, for example pass through your route name so you can generate yoru links using the correct route.

      Then inside your view partial you can use this in the url helper:

      url($this->route, array('page' => $this->first), FALSE, TRUE) ?>
      Don’t forget the additional `order` and `order_by` parameters!

  9. Hello All,

    I am using above process to get the sorting of the data and pagination purpose. but unfortunatly i am getting following error message.

    $table must be a string, array, or an instance of TableIdentifier

    this is due to when calling $select->from($this->table); on AlbumTable.php file.

    Please let me know possible solution for that.

    Regards
    kailash

  10. You forgot to tell to change this code from view/index.phtml:

    ...
    php foreach ($albums as $album): 
    ...

    to:

    ...
    php foreach ($this->paginator as $album):
    ...
  11. Having an issue with this modification. I keep getting a “$table must be a string, array, or an instance of TableIdentifier ” error. Any ideas? I’m stumped

  12. Nice article but I am having trouble with solving this problem:

    I have a filter FORM that posts parameters to the controller so that the result set is limited down. e.g. where orderType = 2.

    What is the solution to sorting on a column whilst retaining the filter? e.g. where orderType=2 ORDER BY orderID ASC

    The problem I have is that when you submit the form (even using METHOD = GET) the controller now has to deal with a query string instead of a route. The parameters from the query string now need incorporating into the “sort” URLs built using url(). I can only think of fudges to solve it.

Leave a Reply

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