PHP / MySQL Pagination

PHP / MySQL Pagination

There is a lot of talk on Webmaster-Talk about how to do pagination. There are a few steps involved with doing so, but with a few custom functions, it can be easy.

We will start with a connection to our database and we can use our MySQL connection class for that:

http://www.webmaster-talk.com/blog/17/php-mysql-connection-class

Let’s introduce the code. It’s a basic PHP class script in which we can include in our web application only once, and we can use over and over with simple configuration on the page scripting side.


class myPagination {

/*
* @return object
* @param string $select_string
* @param string $from_string
* @param string $where_string
* @param int $display_limit
* @desc Get the results of a database query according to the page number
*/
public function ReturnResults ($select_string, $from_string, $where_string = '', $display_limit = 20) {

$this->select_string = $select_string;
$this->from_string = $from_string;
$this->where_string = $where_string;
$this->page = floor(abs((int)$_GET['page']));
if ($this->page < 1) $this->page = 1;
$this->results_total = $this->_count_results();

$this->_get_limits($display_limit);

if ($this->results_total === 0) return false;
return $this->_get_results();
}

/*
* @return int
* @desc Count the number of results returned from the query
*/
private function _count_results () {
global $db;

$count_results = $db->Execute('SELECT COUNT(*) AS count FROM ' . $this->from_string . ( (!empty($this->where_string)) ? ' WHERE ' . $this->where_string : '' ));
return (int)$count_results->fields['count'];
}

/*
* @return object
* @desc Get the results of a database query
*/
private function _get_results () {
global $db;

return $db->Execute('SELECT ' . $this->select_string . ' FROM ' . $this->from_string . ( (!empty($this->where_string)) ? ' WHERE ' . $this->where_string : '' ) . ' LIMIT ' . $this->limit_start . ',' . $this->display_limit);
}

/*
* @return bool
* @param int $display_limit
* @desc Set the limit range for the query
*/
private function _get_limits ($display_limit) {

$this->display_limit = floor(abs((int)$display_limit));
if ($this->display_limit < 1) $this->display_limit = 1;
$this->total_pages = ceil($this->results_total / $this->display_limit);
if ($this->page > $this->total_pages) $this->page = $this->total_pages;
$this->limit_start = ($this->page <= 1) ? 0 : ($this->page - 1) * $this->display_limit;
return true;
}

/*
* @return string
* @param int $display_group
* @param string $seperator
* @param string $fstring_pages
* @param string $fstring_current
* @param string $fstring_prev_next
* @desc Return the html for the page links
* $display_group can be set to any number to group links or set to 0 for unlimited
* odd numbers work best
*/
public function GetPageLinks ($display_group = 5, $seperator = ' ', $fstring_pages = '%s', $fstring_current = '%s', $fstring_prev_next = '%s') {

if ($this->total_pages <= 1) return ' ';

if ($display_group > 0) {

$display_range = array($this->page - floor($display_group / 2) + (($display_group % 2 === 0) ? 1 : 0), ($this->page + floor($display_group / 2)));
if ($display_range[0] < 1) {

$display_range[0] = 1;
$display_range[1] = $display_group;
}

if ($display_range[1] > $this->total_pages) {

$display_range[1] = $this->total_pages;
$display_range[0] = $this->total_pages - ($display_group - 1);
}

if ($display_range[0] < 1) $display_range[0] = 1;
if ($display_range[1] > $this->total_pages) $display_range[1] = $this->total_pages;

} else {

$display_range = array(1, $this->total_pages);
}

$output_array = Array();
for($i = $display_range[0]; $i <= $display_range[1]; $i++) {

if ($i == $this->page) {

$output_array[] = sprintf($fstring_current, $i);

} else {

$output_array[] = sprintf('<'.'a href="?page=' . $i . '">' . $fstring_pages . '<'.'/a>', $i);
}
}

array_unshift($output_array, ( $this->page > 1 ? '<'.'a href="?page=' . ($this->page - 1) . '" title=" previous page ">' : '' ) . sprintf($fstring_prev_next, '‹') . ( $this->page > 1 ? '<'.'/a>' : '' ));
array_unshift($output_array, ( $this->page > 1 ? '<'.'a href="?page=1" title=" first page ">' : '' ) . sprintf($fstring_prev_next, '«') . ( $this->page > 1 ? '<'.'/a>' : '' ));
$output_array[] = ( $this->page < $this->total_pages ? '<'.'a href="?page=' . ($this->page + 1) . '" title=" next page ">' : '' ) . sprintf($fstring_prev_next, '›') . ( $this->page < $this->total_pages ? '<'.'/a>' : '' );
$output_array[] = ( $this->page < $this->total_pages ? '<'.'a href="?page=' . $this->total_pages . '" title=" last page ">' : '' ) . sprintf($fstring_prev_next, '»') . ( $this->page < $this->total_pages ? '<'.'/a>' : '' );

return implode($seperator, $output_array);
}

/*
* @return string
* @desc Return the page count string
*/
public function GetPageCount () {

return 'Page ' . $this->page . ' of ' . $this->total_pages . ' (' . $this->results_total . ' results total)';
}
}

We will want to include the above class script in our current page that we are working on. We also will need to include the script to our sql script and start a new connection. It is important that we assign the database object to the var labeled $db (because the pagination class script will reference that var name inside its functions). So on the page we are building, include the following block of code:

<?php

//# Define database connection
define('DB_SERVER', 'localhost');
define('DB_SERVER_USERNAME', 'root');
define('DB_SERVER_PASSWORD', '');
define('DB_DATABASE', 'database_name');

require('mySqlClass.inc.php');
$db = new mysqlClass();
$db->Connect(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD, DB_DATABASE);

require('myPaginationClass.inc.php');
$pagination = new myPagination();

?>

Now we need to make a new request to a returned set of results from the database. The example table in the database will be a members table labeled members and we want to return the username and id.

Let us create that sample query and return the results for our new pagination script to a var labeled $results


// The first parameter is the SELECT string
// The second parameter is the FROM string
$results = $pagination->ReturnResults('username, id', 'members');


If the database query does not have any data to return, it will return a Boolean of FALSE. If this happens, we can display a message to the user that there are no records to display, other wise we can build an html table of the results along with the pagination links.


<?php

if (!$results) {

echo ‘There are no results to display<br />’;

} else {

?>
<div style=”font-size:10pt;”><?php echo $pagination->GetPageCount() . ‘ <br /> ‘ . $pagination->GetPageLinks(); ?></div>
<table border=”1″ cellpadding=”8″ cellspacing=”0″>
<tr style=”background-color:black;color:white;font-weight:bold;”>
<td>Member’s ID</td>
<td>Member’s Username</td>
</tr>
<?php

while(!$results->EOF) {

?>
<tr>
<td><strong><?php echo $results->fields['id']; ?></strong></td>
<td><?php echo $results->fields['username']; ?></td>
</tr>
<?php

$results->MoveNext();
}

?>
</table><br />
<?php

}

?>
Voila! We just successfully displayed a set of results from a sql query and split the results on multiple pages rather than just one page. But what else can we do? We will now look at different ways we can customize the results and format the links…

Customization:

Lets say we want to return a set of results from our members table, but we want to show members that has had their birthday on or before 1980 and we also want to sort alphabetically by the members username. We also want to show 50 to each page instead of the default amount of 20.


// The first parameter is the SELECT string
// The second parameter is the FROM string
// The third parameter is the WHERE string
// The fourth parameter is the display limit number
$results = $pagination->ReturnResults('username, id', 'members', 'members_birthdate < 12-31-1980 ORDER BY username', 50);


Now we want to customize our page links. We want the text to be bold in Arial font in a grey color. We want the previous and next links not to be underlined, and we want the current page to be red in color while the other page links to be black in color. We also want to display all the page links and not just a range of pages separated with the | pipe.


<!--
// The first parameter is the pages to display number - set to 0 to display all pages
// The second parameter is the seperator string
// The third parameter is the pages link format string
// The fourth parameter is the current page format string
// The fith parameter is the prev/next link format string
//-->

<div style=”font-family:arial;color:#999;font-weight:bold;”><?php echo $pagination->GetPageLinks (0, ‘ | ‘, ‘<span style=”color:black;”>%s</span>’, ‘<span style=”color:red;”>%s</span>’, ‘<span style=”text-decoration:none;”>%s</span”‘); ?></div>
We can use the vars from the class script to create additional functions. By knowing the amount of total pages returned from a result, we can build a Jump-To drop-down menu to redirect the user to the selected page:


<select onchange="window.location='?page='+this.value;">
<option selected>Page:</option>
<?php

for($i = 1; $i <= $pagination->total_pages; $i++) echo ‘ <option value=’ . $i . ‘>’ . $i . ‘</option>’ . “\n”;

?>
</select>

Written by mgraphic on April 9th, 2007 with no comments.
Read more articles related to Coding.

Like This Article "PHP / MySQL Pagination?"
Please consider subscribing to our feed & leaving a comment below.

• [+] Del.icio.us: Bookmark this article

Related articles:

• PHP / MySQL Connection Class
  (February 28th, 2007)
• Creating Well-Built Sites
  (February 28th, 2007)