Tags: Zend Framework 1.7 Pagination MySQL SQL Pages Paged Page Next Prev Previous

Just a quick how-to-make a MySQL table paginatior in Zend Framework.

You'll need these as a reference, zend.paginator.usage and zend.db.select.

First in your controller make a link to MySQL and make an SQL select() object (I find this disgusting syntax, but hey ho)... So, in the following code I am in my controllers action method:

public function myTableAction()
{

    $db = Zend_Db::factory( $this->config->db );
       
    $select = $db->select()
            ->from(array('p' => 'people'), array('id','name'))
            ->order('name');
   
    //echo $select->__toString();
    $this->view->paginator = Zend_Paginator::factory($select);
    $this->view->paginator->setCurrentPageNumber($this->_getParam('page'));
}

So you have passed the MySQL syntax object to the paginator. This will add the LIMIT to the SQL and run it. The LIMIT syntax is built up from the page you are on and how many items per page you are displaying, this information is grabbed from the URL.

Next you need to make your view show this data and the:<prev 1 2 3 4 5 next >HTML. The paginatior object we passed to the view in the above code (the second to last line) is an iterator, so we can just loop over it:

OK, so the bit that builds the table is the partialLoop, we pass our pagination object that contains our table rows to the partialLoop iterator. This builds each <tr> row from the paginatior, the paginatior got the data from the MySQL table (after it manipulated your SQL adding a LIMIT 10,10) and got the item count from your table (it altered your query adding a COUNT() and re-queried the DB)

The loop partial looks like this (partial-loops/people.phtml):

<tr>
    <td><?= $this->name ?></td>
</tr>

Note how we have access to the two columns from MySQL as object properties. BTW I keep my view .phtml files in views/scripts and my partials in views/scripts/partials and my loop partials in views/scripts/loop-partials. You don't have to follow what I do, just make sure your paths make sense.

The style of the:<prev 1 2 3 4 5 next >HTML is in the partials/search-pagination.phtml view, this you can copy from this page (zend.paginator.usage -- Example Paginations Controls (at the end of the page)) and save it in the correct script view directory. Mine is a modified version (partials/search-pagination.phtml):

<?php if ($this->pageCount): ?>
<div class="pagination">

<?php if (isset($this->previous)): ?>
  <a href="<?= $this->url(array('page' => $this->previous)); ?>">&laquo; PREV</a> -
<?php endif; ?>

<?php
    /* Page links */
   
    foreach ($this->pagesInRange as $page): ?>
    <a href="<?= $this->url(array('page' => $page)); ?>" <?php if($page == $this->current): ?>id="selected"><?php endif; ?><?= $page; ?></a>
<?php endforeach; ?>

<?php if (isset($this->next)): ?>
 - <a href="<?= $this->url(array('page' => $this->next)); ?>">Next &gt;</a>
<?php endif; ?>

</div>
<?php endif; ?>

That's it really. You'll need a bit of CSS to style the links.

For more info take a look at the Zend Framework wiki: Zend_Paginator

Job done.