Pagination – Page Numbers – PHP & MySQL

Pagination Tutorial

Dividing up page results with pagination

Sometimes, it is necessary to divide multiple results from a MySQL query onto separate pages. This is usually required when dealing with many results from a database table. A long result list may slow down page load as well as put a strain on your MySQL data-base’s performance levels. So pagination – dividing data into separate pages and applying page number with ‘next/previous’ links is a good idea when dealing with large amounts of information.

The following PHP function and code will allowing you to easily add pagination to your results page.

The PHP & MySQL Pagination code

Function:

<?php
function page_numbers($pg, $total)
{
 // PREVIOUS
 if($pg > 1)
 {
 $prev = ($pg - 1);  
 $page_num .= '<a alt="Previous" href="'.$_SERVER['PHP_SELF'].'?pg='.$prev.'">Previous</a>';
 } 

 /// NUMBERS
 for($ix = 1; $ix <= $total; $ix++)
 {    
 if($pg == $ix)
 { $page_num .= '<a><i>'.$ix.'</i></a> '; } 
 else 
 { $page_num .= '<a href="'.$_SERVER['PHP_SELF'].'?pg='.$ix.'"><b>'.$ix.'</b></a> '; }
 }

 // NEXT
 if($pg < $total)
 {
 $next = ($pg + 1);  
 $page_num .= '<a alt="Next" href="'.$_SERVER['PHP_SELF'].'?pg='.$next.'">Next</a>';
 }

return $page_num;
}
?>

MySQL use of page_numbers

<?php
/// ## Put/include your 'page_numbers' function here ## ///

/// DEFINE PAGINATE
if(!isset($_REQUEST['pg'])){ $pg = 1; } else { $pg = $_REQUEST['pg']; } /// GET CURRENT PAGE
$max_results = 25; /// MAXIMUM PER PAGE
$from = (($pg * $max_results) - $max_results); /// DO THE MATHS
$QUERY = "SELECT id FROM my_data"; /// MYSQL QUERY FOR PAGINATION
///

// COUNT TOTAL
$PAG_R = mysql_query($QUERY);
$PAG_N = mysql_num_rows ($PAG_R);
$total_PAG = ceil($PAG_N / $max_results); /// TOTAL NUMBER OF PAGES
//

/// GET DATA
$result = mysql_query($QUERY."LIMIT $from, $max_results");
$num = mysql_num_rows ($result);
///

/// PAGINATION LINKS
if($PAG_N > $max_results)
{
$PAGINATE = "Viewing $pg of $total_PAG | ";
$PAGINATE .= page_numbers($pg,$total_PAG); /// CALL ON THE 'page_numbers' function
}
else { $PAGINATE = "Viewing $num results"; } /// IF LESS THAN MAXIMUM PER PAGE, JUST SHOW NUMBER OF RESULTS
///

/// LOOP THROUGH RESULTS
$i=0;
if($num > 0){
while ($i < $num) {
$my_field = mysql_result($result,$i,"my_field");
$DATA .= "<LI>".$my_field;
++$i;
}}
///

/// PRESENT DATA
echo $PAGINATE."<hr>";
echo $DATA;
///
?>

Leave a Reply

Your email address will not be published. Required fields are marked *