In and Around the Coding World

Innvo.com

Simple PHP & MySQL Pagination

When looking at MySQL output, it is sometimes more convenient to split up the number of records returned into separate pages, and include hyperlinks to further pages in the result set; a layout often referred to as pagination.

The following is an example of such pagination. Change the MySQL query in the example at the foot of the code to see it working for yourself, remembering to connect to your MySQL database beforehand.

<?php
 
function mysql_query_pagination($query_string,$records_per_page)
{
$return = array(2=>'');
 
/* Uses $_GET['page'] to determine which page number you are viewing */
$baseuri = preg_replace(array("'(&|?)page=d+'"),'',$_SERVER['REQUEST_URI']);
    if(strpos($baseuri,'?'))
        { $baseuri .= '&'; }
    else
        { $baseuri .= '?'; }
 
/* If the page is not the first one, generate navigation to link to previous pages */        
    if(isset($_GET['page']) && ctype_digit($_GET['page']) && $_GET['page'] > 0)
    {
    $page = $_GET['page'];
    $return[2] .= '<a href="'.$baseuri.'page=0">First Page</a>.. ';
    $a = 0;
        for($i = ($page > 10 ? $page-10 : 1);$i < $page;$i++)
            {    $return[2] .= '<a href="'.$baseuri.'page='.$i.'">'.$i.'</a> '; }
    }
    else
        { $page = 0; } $return[2] .= ($page > 0 ? '<b>'.$page.'</b>' : 'First Page').' ';
 
/* Get total number of rows to find out how many links to other pages should be generated */    
$all_rows = mysql_query(preg_replace("'SELECT(.+)FROM'ims",'SELECT COUNT(*) FROM',$query_string)) 
    or die(mysql_error());
$all_rows = mysql_fetch_array($all_rows,MYSQL_ASSOC);
/* Perform actual query sent to function */    
$return[3] = mysql_query($query_string.' LIMIT '.($page*$records_per_page).','.$records_per_page) 
    or die(mysql_error());
$return[0] = $all_rows['COUNT(*)'];
    if(!($return[1] = mysql_num_rows($return[3])))
    { RETURN FALSE; }
 
$return[4] = ($page * $records_per_page) + 1;
$i = 0;
$return[4] += $records_per_page;
$x = $page;
    while($return[4] <= $return[0] && $i < 10)
    {
    ++$x;
    $return[2] .= '<a href="'.$baseuri.'page='.$x.'">'.$x.'</a> ';
    $return[4] += $records_per_page;
    $i++;
    }
 
    if($return[4] < $return[0]-1)
        {    $return[2] .= '..<a href="'.$baseuri.'page='.(floor($return[0]/$return[1])).'">Last Page</a> '    }; unset($return[4]);
$return[2] = '<div align="center" id="mysql_nav" class="mysql_nav">
Navigation (Results #'.(($page * $records_per_page) + 1).' to #'.(($page * $records_per_page) + $return[1]).' 
of '.$return[0].'): 
<br />'.$return[2].'</div>';
RETURN $return;
}
 
/*
Example usage
    $mysql_data[0] returns total row count
    $mysql_data[1] returns number of rows returned after pagination
    $mysql_data[2] returns navigation
    $mysql_data[3] returns the query resource to iterate through
*/
 
// 20 rows per page
    if($mysql_data = mysql_query_pagination('SELECT * FROM table',20))
    {
    // Show navigation at top of results
    echo $mysql_data[2];
    echo '<pre>';
        while($row = mysql_fetch_array($mysql_data[3],MYSQL_ASSOC))
        {
        // Your row data ... do stuff here
        echo implode("t",$row)."\n";
        }
    echo '</pre>';
    // Show navigation at bottom of results
    echo $mysql_data[2];    
    }
    else
    {
    echo 'No results returned';
    }
 
?>
 

Previous Article
Forking with PHP from the Command LineNext Article
A Quick and Efficient URL Shortener Script, Using PHP & MySQL

© Innvo.com | Privacy