Latest |Kites |Pictures |Programming |Life
[filed under Programming]Zend Framework MySQL DB Pagination Tutorial

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:

<table class="my_table">
  <thead>
    <tr class="table_head">
      <th>People</th>
    </tr>
  </thead>
  <tfoot>
    <tr class="table_foot">
      <td colspan="4"><?= $this->paginationControl($this->paginator, 'Sliding', 'partials/search-pagination.phtml'); ?></td>
    </tr>
  </tfoot>
 
  <tbody>
    <?= $this->partialLoop('partial-loops/people.phtml', $this->paginator) ?>
  </tbody>
</table>

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.

 

28th of October, 2008@10:15:03 AM
5 comments, permanent link to article

Comments

[28th of Oct, 2008 @ 03:12 PM]
Simon said:
"make an SQL select() object (I find this disgusting syntax, but hey ho)..."

Why so?
[28th of Oct, 2008 @ 03:23 PM]
Compare this:

$select = $db->select()
->from(array('p' => 'people'), array('id','name'))
->order('name');

To this:

SELECT id, name FROM people ORDER BY name

I think the second is easier to read. PHP should make SQL part of the language, a mini language, so you could do this:

$s = SELECT id, name FROM people ORDER BY name;

(note: no quotes)

echo $s->order_by;

$s->order_by->append( 'id' );

or something like that.
[9th of Nov, 2008 @ 09:43 AM]
Martin Winkel said:
@monk.e.boy

I agree it's far more readable, but on the other hand, there are tiny differences between database-systems in the SQL language. Using a tiny language will not solve that problemn, while a select-object does.

Anyway, I hope this stays just here ;).

Usefull tutorial by the way.
[18th of Feb, 2009 @ 08:36 AM]
dayg said:
Thanks for the tutorial! It works.

Minor correction for the navigation:

id="selected"><?php endif; ?>

Should be

id="selected"<?php endif; ?>>
[16th of Dec, 2009 @ 12:56 PM]
said said:
not working. returns this error : Message: No paginator instance provided or incorrect type

Comment

Sorry, but until I can figure out how to stop the spam, comments are disabled :(

Server Grind [0.1671 seconds]