simple paging with PHP

Question

Hi,

I would like to implement paging in PHP. I have some result set from db, let it be an array. I need a paging that should display 4 records per page and the page numbers should be of the following

"<< 1,2,3.....20 >>"

and when you select page 2, then the format should be of the following

"<< 3,4,5.....20 >>"

can you guys suggest me some paging concepts to implement this?

Answer 1

Look into PEAR::Pager.

Answer 2

You can actually use the LIMIT clause in SQL for this.

Here's a decent tutorial to get you started.

Answer 3

I would recommend using a standardized library. Ignacio reccomended the PEAR::Pager library, but I'm partial to Zend_Paginator in the Zend Framework.

There's a wide variety of adapters included in the library, and on Github by others for using different data sources.

Answer 4

It will be most efficient if you manage this in the Db and in the data access code u can have a function that takes arguments like pageSize,PageNumber,sortOrder. Your sql could look something like

SELECT * FROM your_table WHERE condition = true ORDER BY some_field LIMIT 100, 10

Answer 5

Getting the results is simple enough, using limit as noted above based on the page number. You can use a loop to print out the page numbers.

First, say the page number is sent as page=2 in the query string.

$pagenum=(int)$_GET['page'];
if($pagenum<1){ $pagenum=1; }//perform a sanity check. You might also query to find the max page number and see that it's not higher than that.

You then insert the pagenum into your SQL query as an offset, after multiplying it by the number of results per page minus the results per page.

In almost every case one should use a prepared statement for putting using supplied parameters into SQL of course, however in this case it's not strictly necessary since you are sure the variable is an int due to casting (the (int) part). Just had to emphasize that.

Say you have 4 items per page. You can either use two arguments to LIMIT, or LIMIT and OFFSET separately. If you use just limit, the first number is the offset, second is the number of results.

$offset=$pagenum*4-4;//this means for page 1, start on 0, page 2 starts on 4, etc.
$sql="select * from the_table limit $offset,4";

So, that's how you get the data for a given page number. Then, printing out the page numbers is another story. This example is based on how you want the pages to look as described above.

for($i=pagenum+1;$i<20;$i++){
  if($i<$pagenum+4){  ?>
  <a href='stuff.php?page=<?php echo $i;?>'><?php echo $i;?></a>
  <?php } elseif($i==$pagenum+4) { ?>
  ... <?php } elseif($i==20){ ?>
  <a href='stuff.php?page=20'>20</a>
  <?php } ?>