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. This code is designed for simplicity rather than considering the finer details of pagination (mentioned below).

First off, create a test table if you wish to test the code:

CREATE TABLE test_table (
	id int(10) unsigned NOT NULL,
	data varchar(32) NOT NULL,
	PRIMARY KEY (id)
) ENGINE=MyISAM;

Add some test data

INSERT IGNORE test_table VALUES (1,'Row 1'),(2,'Row 2'),(3,'Row 3'),(4,'Row 4'),(5,'Row 5'),(6,'Row 6'),(7,'Row 7'),(8,'Row 8'),(9,'Row 9'),(10,'Row 10'),(11,'Row 11'),(12,'Row 12'),(13,'Row 13'),(14,'Row 14'),(15,'Row 15'),(16,'Row 16'),(17,'Row 17'),(18,'Row 18'),(19,'Row 19'),(20,'Row 20'),(21,'Row 21'),(22,'Row 22'),(23,'Row 23'),(24,'Row 24'),(25,'Row 25'),(26,'Row 26'),(27,'Row 27'),(28,'Row 28'),(29,'Row 29'),(30,'Row 30');

This is the simple PHP class to illustrate basic pagination:

class pagination {

    static $db;
    static $result;
    static $allrows;
    static $remainingrows;
    static $html = '';
    
    
    static function perform($query_string,$records_per_page) {
            
            // Change these details to your own host,username, password and database
            self::$db = new mysqli('localhost','username','password','dbname');
                if(self::$db->connect_errno) 
                    die("Failed to connect to MySQL: (" . self::$db->connect_errno . ") " . self::$db->connect_error);
        
            // Uses $_GET['page'] to determine which page number you are viewing
            $baseuri = preg_replace(array("'(&|\?)page=\d+'"),'',$_SERVER['REQUEST_URI']);
            $baseuri .= (strpos($baseuri,'?') ? '&' : '?');

            self::$html = '';
         
            // Get total number of rows to find out how many links to other pages should be generated 
            self::$allrows = array_shift(self::$db->query(preg_replace("'SELECT(.+)FROM'ims",'SELECT COUNT(*) FROM',$query_string))->fetch_array(MYSQLI_ASSOC));
    
            // See if there is the pagination var present in the current URI
            if(isset($_GET['page']) && ctype_digit((string) $_GET['page']) && $_GET['page'] > 0) 
                $page = $_GET['page'];
            else
                $page = 0;
                
            // Perform query
            self::$result = self::$db->query($query_string.' LIMIT '.($page*$records_per_page).','.$records_per_page);
        
            // Check there's at least 1 result
            if((self::$remainingrows = self::$result->num_rows) < 1)
                return false; 
            
            // If this is not the first page, generate navigation to link to previous pages 
            if($page) {
                self::$html .= '<a href="'.$baseuri.'page=0">First Page</a>.. ';
                $a = 0;
                for($i = ($page > 10 ? $page-10 : 1);$i < $page;$i++)
                    self::$html .= '<a href="'.$baseuri.'page='.$i.'">'.$i.'</a> ';
            }

            // The current page is not a clickable pagination link
            self::$html .= ($page > 0 ? '<b>'.$page.'</b>' : 'First Page').' ';
             
            
            $increment = ($page * $records_per_page) + 1;
            $i = 0;
            $increment += $records_per_page;
            $x = $page;
    
            // Pagination to next results
            while($increment <= self::$allrows && $i < 10) {
                ++$x;
                self::$html .= '<a href="'.$baseuri.'page='.$x.'">'.$x.'</a> ';
                $increment += $records_per_page;
                $i++;
            }
            
            // Link to the last page of results
            if($increment < self::$allrows-1)
                self::$html .= '... <a href="'.$baseuri.'page='.(floor(self::$allrows/self::$remainingrows)).'">Last Page</a> '; 
    
            self::$html = '<div align="center" class="mysql_nav">
Navigation (Results #'.(($page * $records_per_page) + 1).' to #'.(($page * $records_per_page) + self::$remainingrows).' of '.self::$allrows.'):
<br />'.self::$html.'
</div>';
    }
}


$pagination = pagination::perform('SELECT * FROM test_table',5);
echo pagination::$html;

Produces something like…

<div align="center" class="mysql_nav">
Navigation (Results #1 to #5 of 30):
<br />First Page <a href="?page=1">1</a> <a href="?page=2">2</a> <a href="?page=3">3</a> <a href="?page=4">4</a> <a href="?page=5">5</a> 
</div>

In most cases and in particular for small tables, this method of pagination is fine as it’s a relatively inexpensive computation and allows you to jump to any page you like.

For larger tables you will find that an alternative method is preferred, this post goes into detail why. The post is useful in understanding the general concepts regarding performance and pagination from the MySQL point of view.