Simple pagination in PHP using Mysql

September 12th, 2009No Comments

This is the simple example about how to do pagination in PHP.

/*
Function to get simple query converted into pagination query
*/

function getPagingQuery($sql, $itemPerPage = 10)
{
if (isset($_GET['page']) && (int)$_GET['page'] > 0) {
$page = (int)$_GET['page'];
} else {
$page = 1;
}
// start fetching from this row number
$offset = ($page – 1) * $itemPerPage;
return $sql . "LIMIT $offset, $itemPerPage";
}

/*
Main function to do the pagination
Get the links to navigate between one result page to another.
Supply a value for $strGet if the page url already contain some
GET values for example if the original page url is like this :

http://www.abc.com/index.php?c=12

use "c=12" as the value for $strGet. But if the url is like this :

http://www.abc.com/index.php

then there's no need to set a value for $strGet
*/

function getPagingLink($sql, $itemPerPage = 10, $strGet = '')
{
$result = dbQuery($sql);
$pagingLink = '';
$totalResults = dbNumRows($result);
$totalPages = ceil($totalResults / $itemPerPage);

// how many link pages to show
$numLinks = 10;

// create the paging links only if we have more than one page of results
if ($totalPages > 1) {

$self = 'http://' . $_SERVER['HTTP_HOST'] . $_SERVER['PHP_SELF'] ;

if (isset($_GET['page']) && (int)$_GET['page'] > 0) {
$pageNumber = (int)$_GET['page'];
} else {
$pageNumber = 1;
}

// print 'previous' link only if we're not
// on page one
if ($pageNumber > 1) {
$page = $pageNumber – 1;
if ($page > 1) {
$prev = " <a href=\"$self?page=$page&$strGet/\">[Prev]</a> ";
} else {
$prev = " <a href=\"$self?$strGet\">[Prev]</a> ";
}

$first = " <a href=\"$self?$strGet\">[First]</a> ";
} else {
$prev = ''; // we're on page one, don't show 'previous' link
$first = ''; // nor 'first page' link
}

// print 'next' link only if we're not
// on the last page
if ($pageNumber < $totalPages) {
$page = $pageNumber + 1;
$next = " <a href=\"$self?page=$page&$strGet\">[Next]</a> ";
$last = " <a href=\"$self?page=$totalPages&$strGet\">[Last]</a> ";
} else {
$next = ''; // we're on the last page, don't show 'next' link
$last = ''; // nor 'last page' link
}

$start = $pageNumber – ($pageNumber % $numLinks) + 1;
$end = $start + $numLinks – 1;

$end = min($totalPages, $end);

$pagingLink = array();
for($page = $start; $page <= $end; $page++) {
if ($page == $pageNumber) {
$pagingLink[] = " $page "; // no need to create a link to current page
} else {
if ($page == 1) {
$pagingLink[] = " <a href=\"$self?$strGet\">$page</a> ";
} else {
$pagingLink[] = " <a href=\"$self?page=$page&$strGet\">$page</a> ";
}
}
}
$pagingLink = implode(' | ', $pagingLink);

// return the page navigation link
$pagingLink = $first . $prev . $pagingLink . $next . $last;
}
return $pagingLink;
}

/************************************************

This will come in a page where you want to do the pagination

$queryString = '';
// for paging
// how many rows to show per page
$rowsPerPage = 10;

$sql = "SELECT * FROM tbl_members ORDER BY name";
$result = mysql_query(getPagingQuery($sql, $rowsPerPage));
$pagingLink = getPagingLink($sql, $rowsPerPage);

/*********************************************************

Where you want to display the pagination write the code below

<?php echo $pagingLink; ?>

Related Posts:

Tagged : , ,

No Responses

Leave a Reply