PHP Pagination with mysqli
I’m asked pretty regularly for help with pagination in PHP. I have a nice mysqli pagination class that I use. Once it’s implemented, it is used like this:
/**
* $db is your mysqli connection
* $query is your query
* 'mailPage' is the $_GET variable to use for the pages. It defaults to 'page' but using different values lets you have separate
* paginated data on that same web page. Setting multiple paginated items on the same webpage to the same "page" variable
* will mean that when one goes to page two, all of them go to page 2.
*/
// Require the class
require_once('paginate.class.php');
// Lets not use your normal SELECT * FROM table query...just to show it still works
$query = <<<EOQ
SELECT
`mail`.`id`,
`mail2`.`read`,
`mail2`.`seen`,
`mail`.`from`,
`mail`.`sent`,
`mail`.`subject`,
`mail`.`body`,
`users`.`full_name` as from_name
FROM `mail`
JOIN `users`
ON (`mail`.`from` = `users`.`id`)
JOIN `mail2`
ON (`mail`.`id` = `mail2`.`mess_id`)
WHERE
`mail2`.`uid`=1 &&
`mail2`.`folder`=1
ORDER BY
`mail`.`sent` DESC
EOQ;
// Create our paginate object, and pass it the database, query, and page var
$paginate = new Paginate($db, $query, 'mailPage');
// Run the query
$r = $paginate->get_results();
// Loop through our results
while ($message = $r->fetch_object()) {
// Create your page of information
}
// Show the page links
echo $paginate->show_pages();
Now for the actual code of the class:
<?php
/**
* paginate.class.php - PHP 5 class for pagination of mysqli results
*
* @author Aaron D. Campbell <AaronCampbell@ezdispatch.com>
*
* @version 1.0.4
*
* Changes:
* 1.0.4:
* Added urlencoding to each get value when generating the links
* 1.0.3:
* Added show_result_text() which displays a string like this: Showing __-__ of ___ total
* Changed show_pages() to return only the links without the containing div
* 1.0.2:
* Added the ability to specify what $_GET variable is used to track the current page (allows for multiple instances on one webpage)
* Added "first" and "last" links to the page links
* 1.0.1:
* Implemented more strict error checking on the page variable
* Changed PHP_SELF variables into SCRIPT_NAME to avoid vulnerabilities ( http://blog.phpdoc.info/archives/13-guid.html )
* 1.0.0: original version
*/
class Paginate {
//mysqli connection resource
private $db;
//MySQL query
private $q;
//number of rows per page.(default to 20)
private $per_page = 20;
//$_GET index to use for tracking page number
private $page_var = 'page';
//number of rows returned by the query.
private $total_rows;
//total number of pages.
private $total_pages;
//number of page links to show on either side of the current page.
private $links_each_side = 3;
//maximum number of pages to just show all, before using ellipses
private $max_show_all = 10;
/**
* Set everything up.
*
* @param mysqli_resource $db - mysqli connection resource
* @param string $q - MySQL query
* @param string[optional] $page_var - $_GET/$_SESSION['paginate_class'] index to use for "Current Page" defaults to 'page'
*
* @return void
*/
public function __construct($db, $q, $page_var='page') {
$this->db = $db;
$this->q = $q;
$this->page_var = $page_var;
$this->check_page_var();
}
/**
* Make sure that the current page is valid (whole number >= 1)
*/
public function check_page_var() {
/*
* if no page is specified, set it to 1. If it IS specified, force it to int, and make sure
* it is >= 1
*/
if (isset($_GET[$this->page_var]) && (int)$_GET[$this->page_var] > 0) {
$_SESSION['paginate_class'][$this->page_var] = (int)$_GET[$this->page_var];
} elseif (!isset($_SESSION['paginate_class'][$this->page_var])) {
$_SESSION['paginate_class'][$this->page_var] = 1;
}
if ($this->per_page != 0) {
$this->create_query();
}
}
/**
* Set the number of results returned per page.
*
* @param Unsigned Int $per_page
*/
public function set_per_page($per_page) {
$this->per_page = abs((int) $per_page);
}
/**
* Set the number of page links to show on either side of the current page.
*
* @param Unsigned Int $links_each_side
*/
public function set_links_each_side($links_each_side) {
$this->links_each_side = abs((int) $links_each_side);
}
/**
* Set maximum number of pages to just show all, before using ellipses
* 0 will show all no matter what
*
* @param Unsigned Int $max_show_all
*/
public function set_max_show_all($max_show_all) {
$this->max_show_all = abs((int) $max_show_all);
}
/**
* Used to get the result set, and set the Total rows.
*
* @return mysqli result object
*/
public function get_results() {
$return = $this->db->query($this->q);
//get the number of rows that WOULD have been returned if there was no LIMIT
//This is done by using FOUND_ROWS() after using SQL_CALC_FOUND_ROWS in the query
$this->total_rows = array_pop($this->db->query('SELECT FOUND_ROWS()')->fetch_row());
return $return;
}
/**
* Show current results being viewed and total
*
* @return string - Showing __-__ of ___ total
*/
public function show_result_text() {
$start = (($_SESSION['paginate_class'][$this->page_var]-1) * $this->per_page)+1;
$end = (($start-1+$this->per_page) >= $this->total_rows)? $this->total_rows:($start-1+$this->per_page);
return "Showing {$start}-{$end} of {$this->total_rows} total";
}
/**
* Creates links to other pages.
*
* @return string - page links
*/
public function show_pages() {
//If number of rows per page is 0 (unlimited), return an empty string.
if ($this->per_page == 0) {
return '';
}
/*
* If the user did not run get_results, we run it. We could modify the query
* to remove the SQL_CALC_FOUND_ROWS, and the limit...and either make it into
* a count() query, or check num_rows...but it's a lot easier to just call
* get_results with the query as it is.
*/
if (!isset($this->total_rows)) {
$this->get_results();
}
//calculate the number of pages.
$this->total_pages = ceil($this->total_rows/$this->per_page);
//we use this array to store the page links that we want...so we can implode on |
$page_array = array();
$first = $this->get_page_link(1, '<< First');
$last = ($this->total_pages > 1)? $this->total_pages:1;
$last = $this->get_page_link($last, 'Last >>');
//if the number of pages is not more than the max that was specified, add
//all the pages.
if ($this->total_pages <= $this->max_show_all || $this->max_show_all == 0) {
for ($i=1; $i<=$this->total_pages; $i++) {
$page_array[] = $this->get_page_link($i);
}
} else {
/*
* make sure that page one gets in...but only if it wouldn't make it on
* it's own...we don't want it there twice.
*/
if($_SESSION['paginate_class'][$this->page_var] >= $this->links_each_side+2) {
$page_array[] = $this->get_page_link(1);
}
// If needed, add an ellipsis after page one.
if($_SESSION['paginate_class'][$this->page_var] >= $this->links_each_side+3) {
$page_array[] = '...';
}
//Set the first page to be added (for pages in the main group)
if ($_SESSION['paginate_class'][$this->page_var]-$this->links_each_side <= 1) {
$start = 1;
} elseif ($_SESSION['paginate_class'][$this->page_var] > $this->total_pages-$this->links_each_side) {
$start = $this->total_pages-(2*$this->links_each_side);
} else {
$start = $_SESSION['paginate_class'][$this->page_var]-$this->links_each_side;
}
//Set the last page to be added (for pages in the main group)
if ($_SESSION['paginate_class'][$this->page_var]+$this->links_each_side >= $this->total_pages) {
$end = $this->total_pages;
} elseif ($_SESSION['paginate_class'][$this->page_var] < $this->links_each_side+1) {
$end = (2*$this->links_each_side)+1;
} else {
$end = $_SESSION['paginate_class'][$this->page_var]+$this->links_each_side;
}
//add the pages for the main group.
for ($i=$start; $i<=$end; $i++) {
$page_array[] = $this->get_page_link($i);
}
// If needed, add an ellipsis before the last page.
if($_SESSION['paginate_class'][$this->page_var] <= $this->total_pages-$this->links_each_side-2) {
$page_array[] = '...';
}
/*
* make sure that the last page gets in...but only if it wouldn't make it
* on it's own...we don't want it there twice.
*/
if($_SESSION['paginate_class'][$this->page_var] <= $this->total_pages-$this->links_each_side-1) {
$page_array[] = $this->get_page_link($this->total_pages);
}
}
//implode the links and ellipses into a | seperated string, and center in a div
return "{$first} ".implode(' | ',$page_array)." {$last}";
}
/**
* Creates a page link, including the current $_GET string...updating only
* $_SESSION['paginate_class'][$this->page_var]. It also returns only text (no link) if $p is the current
* page.
*
* @param int $p - Page number to create link for
* @return string - Link
*/
private function get_page_link($p, $text=NULL) {
if ($text === NULL) {
$text = $p;
}
if ($p != $_SESSION['paginate_class'][$this->page_var]) {
$_GET[$this->page_var] = $p;
$get_string = array();
foreach ($_GET as $k=>$v) {
if (is_array($v)) {
foreach ($v as $cur_v) {
$cur_v = urlencode($cur_v);
$get_string[] = "{$k}[]={$cur_v}";
}
} else {
$v = urlencode($v);
$get_string[] = "{$k}={$v}";
}
}
$get_string = implode('&',$get_string);
return "<a href=\"{$_SERVER['SCRIPT_NAME']}?{$get_string}\">{$text}</a>";
} else {
return $text;
}
}
/**
* Adds the proper LIMIT to the query, as well as adding SQL_CALC_FOUND_ROWS
* which is used to get the total number of rows (ignoring LIMIT) without
* doing a count() query
*/
private function create_query() {
//calculate the starting row
$start = ($_SESSION['paginate_class'][$this->page_var]-1) * $this->per_page;
//insert SQL_CALC_FOUND_ROWS and add the LIMIT
$this->q = preg_replace('/^SELECT\s/i', 'SELECT SQL_CALC_FOUND_ROWS ', $this->q)." LIMIT {$start},{$this->per_page}";
}
}
?>
Thanks for your class – seems to be very useful for me, but I have problem to start work with. Could you help me, please, with some additional comments (or examples) “how to use this”????
Best regards,
Mrozko
I updated the example to show a more reasonable use case. I hope that helps.
Hello!
I'm not use the class already, but in principle seems a good code! Thanks for share this… and sorry for my bad english, i'm writing this text witought dictionary, so … ¡Thanks again!
Good Explain..
Hello, I tried to use this class and set_per_page to something less than 20. It worked for the page links, however the results were not changed. I discovered this is because the query isn’t recreated. I got it working with a minor modification. I stored the original query and call create_query after setting the per_page value. Other than that, this class is very useful. Thanks.