Hollen-B Web and Graphic Design Portfolio

Pagination with PHP and MySQL

In this tutorial I will show you how to take a list of states from a database and break up the states into groups of 10 per page.

Create Database

First we need to create a database that we can extract our data from. I put up a short script you can copy from my google docs, paste it into a php file and run it to create the database table and fields.

For a copy of the database script Click Here...

Open Connection to your Database.

Since we are collecting our data from the database we need to open a connection.

PHP

<?php
// OPEN DATABASE
 define("HOSTNAME","localhost");
 define("USERNAME"," *** USERNAME HERE *** ");
 define("PASSWORD"," *** PASSWORD HERE *** ");
 define("DATABASE"," *** DATABASE HERE *** ");
 
 mysql_connect(HOSTNAME, USERNAME, PASSWORD) or die("Connetion to database failed!");
 mysql_select_db(DATABASE); 
?>

Pagination Script

In this script we are going to determine how much data we want to display per page.

PHP

<?php
// ROWS DISPLAYED PER PAGE
 $rows_per_page = 10;
 
// GET PAGE NUMBER
 $page = $_GET['page'];
 $offset = (!empty($page)) ? $page : $page = 1;
 
// URL CLEAN UP
 $self = $_SERVER['PHP_SELF']."?".$_SERVER['QUERY_STRING'];
 $self = str_replace("page={$offset}", "", $self); 
 
// GET LIST OF STATES
 $offset = ($page) ? ($page - 1) * $rows_per_page : 0;
 $query = "SELECT * ".
 "FROM states_list ".
 "ORDER BY id ".
 "LIMIT {$offset},{$rows_per_page}";
 $result = mysql_query($query);
 
// GET NUMBER OF PAGES
 $ltq = mysql_query("SELECT * FROM states_list");
 $listings_total = mysql_num_rows($ltq);
 $NumPgs = ceil($listings_total / $rows_per_page);
?>
 
<div style="width: 500px; margin:auto; border:1px #666666 solid;">
 <div style="background:#CCCCCC; text-align:center; padding:4px; border-bottom:1px #666666 solid;"><strong>LIST OF STATES</strong></div>
 <?php while($row = mysql_fetch_array($result)){ ?>
 <div style="text-align:center; padding:4px; border-bottom:1px #666666 solid;"><?=$row['states']?></div>
 <?php } ?>
 <span style="float:right">
 <?=$prev = ($NumPgs > 0 && $page!=1) ? "<a href=\"{$self}page=".($page-1)."\">&lt;&lt;Prev</a>&nbsp;&nbsp;": 
 "&lt;&lt;Prev&nbsp;&nbsp;"; ?>
 [Page <?php echo $page; ?>]
 <?=$next = ($page < $NumPgs) ? "<a href=\"{$self}page=".($page+1)."\">&nbsp;&nbsp;Next&gt;&gt;</a>": 
 "&nbsp;&nbsp;Next&gt;&gt;";?>&nbsp;
 </span>
 <b>&nbsp;<?=$offset+1?> to <?=$offset+$rows_per_page?>, of <?=$listings_total?> States</b>
</div> 
?>

Rows Displayed Per Page / Line 3:

In this script we are going to determine how much data we want to display per page.

php

$rows_per_page = 10;

Get Page Number / Lines 6-7:

Since we are using URL parameters to get our page number, we need to to check the URL and see if our page param is empty or not. So, for example, if the URL to our pagination page is http://www.hollen-b.com/tutorial/pagination_demo.php we will default to page 1. If the URL to our pagination page is http://www.hollen-b.com/tutorial/pagination_demo.php?page=2 we go to page 2.

php

$page = $_GET['page'];
 $offset = (!empty($page)) ? $page : $page = 1;

URL Clean Up / Lines 10-11:

Without this every time someone clicks a prev or next link our parameters will start to stack up in our URL. So we need to get the URL and remove the old page parameter.

php

$self = $_SERVER['PHP_SELF']."?".$_SERVER['QUERY_STRING'];
 $self = str_replace("page={$offset}", "", $self);

Get List Of States / Lines 14-18:

Every time we load the page we are going to run two arguments to MySQL using LIMIT X, Y. X is the starting point, and Y is the total amount of records we want to display. To get X in our case is , we need to first get the page number were on. Subtract the page number by 1, multiply that number by the total rows per page and we now should have the X value we need. Y is simply our number of rows we want to display which was set on line 3.

php

$offset = ($page) ? ($page - 1) * $rows_per_page : 0;
 $query = "SELECT * ".
 "FROM states_list ".
 "ORDER BY id ".
 "LIMIT {$offset},{$rows_per_page}";
 $result = mysql_query($query);

Get Number Of Pages / Line 22-24:

If we query the database for our total number of rows, we can divide that by the total rows per page, which should give us our total number of pages. By using ceil function we can round our total number of pages to the next highest integer.

php

$ltq = mysql_query("SELECT * FROM states_list");
 $listings_total = mysql_num_rows($ltq);
 $NumPgs = ceil($listings_total / $rows_per_page);

Comments