PHP / MySQL Pagination

Posted on 2011-02-12
Last Modified: 2012-05-11
Hey experts.  I've been getting my feet wet this week with integrating php and mysql in to our site and went through a few tutorials at  The code below is based on what I learned in the php tutorial there.  I need to limit data being returned to 20 rows per page and include some standard "Next", "Previous" and "Page Number" links at the top and bottom of the page.  I'm getting the data back from the query but I get all the data in that table...I seem to be failing to grasp something here because I just keep blowing up the page.  Can someone help?  Thanks in advance!  Here's the page pulling data from the database:

<?php require_once("includes/connection.php"); ?>
<?php require_once("includes/functions.php"); ?>
<?php find_selected_page(); ?>
<?php include("includes/header.php"); ?>
  <table id="structure" height="100%">
      <td id="navigation"><?php echo public_navigation($sel_subject, $sel_page); ?> <BR />
        <BR />
        <a href="login.php"><strong>Log In</strong></a><BR />
        <a href="displayUsers.php"><strong>Display Users</strong></a> <BR /></td>
      <td id="page"><h2>The following users are active in the Contacts table:</h2>
        <p>This site house confidential client information for the John 3:16 Mission, Inc Family &amp; Youth Center.</p>
	$result = mysql_query("SELECT * FROM contacts", $connection);
	if (!$result) {
		die("Database query failed: " . mysql_error());

echo "<table border='1'>
<th>User ID</th>
<th>User Type</th>
<th>First Name</th>
<th>Last Name</th>

while($row = mysql_fetch_array($result))
echo "<tr>";
echo "<td>" . $row['ContactID'] . "</td>";
echo "<td>" . $row['ContactType'] . "</td>";
echo "<td>" . $row['FirstName'] . "</td>";
echo "<td>" . $row['LastName'] . "</td>";
echo "<td>" . $row['DateOfBirth'] . "</td>";
echo "<td>" . $row['Employer'] . "</td>";
echo "<td>" . $row['BackgroundCheck'] . "</td>";
echo "<td>" . $row['OrientationDate'] . "</td>";
echo "<td>" . $row['Comments'] . "</td>";
echo "<td>" . $row['DateEntered'] . "</td>";					  
echo "</tr>";
echo "</table>";

  <?php include("includes/footer.php"); ?>

Open in new window

Question by:nikaotech
  • 3
  • 2
  • 2
  • +2
LVL 83

Expert Comment

by:Dave Baldwin
ID: 34879869
Alter your query like this where $cpos is your current starting position and 20 is the number of rows.  You manipulate $cpos to go back and forth.  Make it $cpos-20 to go back a page and $cpos+20 to go forward a page.  You should probably check to make sure $cpos doesn't go negative or past the end of the table.
$result = mysql_query("SELECT * FROM contacts" LIMIT $cpos,20", $connection);

Open in new window

LVL 83

Expert Comment

by:Dave Baldwin
ID: 34879872
Oops, extra quote above.
$result = mysql_query("SELECT * FROM contacts LIMIT $cpos,20", $connection);

Open in new window


Author Comment

ID: 34879944
Hey Thanks Dave!  I got the following error:

Database query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '20' at line 1

So as a newbie I just tried removing the comma between "$cpos" and "20" I get 20 rows, but no prev/next...
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

LVL 83

Expert Comment

by:Dave Baldwin
ID: 34880139
You have to give "$cpos" a value and add the logic/arithmetic to create 'prev' and 'next' links yourself.
LVL 20

Expert Comment

by:Mark Brady
ID: 34880182
You have to write the prev/next yourself as links. What happens is your query returns the first 20 results and then it is done (finished). That query is no more. It doesn't create anything other than it is holding the results of the query ready for you to use. So go ahead and list out your results on the page for people to see, then at the bottom (or where-ever you want them to be, put two links. Direct them to your page (the same page) and parse a number value in the URL which will tell your php code whhere to start the next query. If you post the structure of your table here and tell us how you would like the results sorted (sort by surname for eg) we can write the query properly for you and include some code to give you the previous and next page links. By the way, you can read all about Pagination here

Pagination is the name given to creating links for previous and next and pages in between if you want. Read the tutorial and you will know how to do this yourself. Lastly, I usually add a small selectbox to the page to allow the users to select how many results they see per page. Everyone is different. Some will want only 10 results (my wife prefers 1 result per page!!!), some will want 50 and others will want ALL results on one page. So create a selectbox and have it submit as soon as the user makes a selection. You can store the results one of 2 ways.

1: If they have a user account, you could add a column that stores their preference for how many results to view per page. I do this method. Create a column called 'results' and make it int(4) so they can choose up to 9999 results per page!

2: If they don't have user accounts, store the value in a session variable.

First you need to create some code on your page to see if there has been a selection made, or if the results has been set. The "selectbox" is create further down in my posting. Here is the code to check if a form has been submitted:

if(isset($_POST['results'])){ // the form has been submitted
$results_per_page = $_POST['results'];
}else{ // the form has NOT been submitted so make a default value
$results_per_page = 20;

Now if the selectbox is changed, the new value will be stored in the $results_per_page variable. If you have user accounts and want to store that value in the users account the above code would be changed to add an update query similar to this:

if(isset($_POST['results'])){ // the form has been submitted
$results_per_page = $_POST['results'];
mysql_query("UPDATE users SET results = $results_per_page WHERE user = '$username'")or die(mysql_error());
}else{ // the form has NOT been submitted so make a default value
$results_per_page = 20;

If you are storing the  users results in a users table you would do this:
Assuming you know what their username is. Let's say you are storing their username in a variable $username when they login the query would be as follows:

$select = "<select name='results' onchange='this.form.submit()'>";
$select .= "<option value=''>Choose results per page</option>";
$sql = "SELECT results FROM users WHERE username = '$username' LIMIT 1";
$result = mysql_query($sql)or die(mysql_error());
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
$results_per_page = "{$row['results']}";
if($results_per_page == 10){
$select .= "<option selected value=10>10</option>";
$select .= "<option value=10>10</option>";
if($results_per_page == 20){
$select .= "<option selected value=20>20</option>";
$select .= "<option value=20>20</option>";
if($results_per_page == 30){
$select .= "<option selected value=30>30</option>";
$select .= "<option value=30>30</option>";

// Ok now we know what that user likes, we can use it in your pagination code. So place this code (the code above) on the page that you do your searches. If you don't have "user" table to keep user information (no login feature) then you could store the value (if selected) in a session variable. That way, when the page first loads, you will have a default value for search results and if the user changes the dropdown box then it will remain that value on all other search pages as long as you create a session on each page. Let me know if you would like to use sessions and I'll show you how.

For now, show the dropdown box like this:

echo $select;

That's it! oh, make sure the $select is inside a <form></form> tag so it submits the form. Something like this:

echo "<form method='POST' >";
echo $select;
echo "</form>";

Now it will submit the choice back to the same page. Sorry if all this seems confusing. If you can't figure out where to put all this code let me know and I will write it neatly for you!

LVL 109

Accepted Solution

Ray Paseur earned 500 total points
ID: 34880317
The canonical article on PHP pagination (nicely packaged in object-oriented programming) is available here, from SitePoint:

Expert Comment

ID: 34884062
Here is a script you can use for pagination:


function setQueries($num) {
	global $num_queries;
	$num = @mysql_fetch_array($num);
	$num_queries = $num[0];
function makePages($force="") {
	/* Returns NULL
	 * Outputs HTML
	 * Used for pagination.
	global $num_queries, $ITEMS_PER_PAGE;
	$pg = !$_GET['pg']?1:$_GET['pg'];
	if (isset($ITEMS_PER_PAGE)) {
		$ipp = $ITEMS_PER_PAGE;
	$num = $num_queries;
	$url = $_SERVER["SCRIPT_URI"];
	if ($force) $url = $force;
	if (!strstr($url,"?")) $url .= "?_/pages";
	foreach ($_GET as $key=>$value) { if ($key!="pg" AND $key != "_" AND $key != '' AND !strstr($key,'/')) $url.="&$key=".urlencode($value); }
	$url .= "&pg=";
	$num = $num_queries;
	$page = $pg;
	$pages = ceil($num/$ipp);
	$left = 5;
	$right = 5;
	$echo = "<div class=\"spaceman\"></div><div class=\"alignCenter pager\">";
	if ($page > 1) {
		$echo .=  "<a href=\"$url".($page-1)."\">&lt;&lt; Prev</a> ";
	if ($page > $left+1) {
		$echo .=  "<a href=\"$url".(1)."\">1</a> ... ";
	for ($x=$left;$x>0;$x--) {
		if (($page-$x) < 1) { $right++; continue; }
		$echo .=  "<a href=\"$url".($page-$x)."\">".($page-$x)."</a> ";
	$echo .=  " <strong id=\"selected\">$page</strong> ";
	for ($x=1;$x<$right;$x++) {
		if (($page+$x) > $pages) break;
		$echo .=  "<a href=\"$url".($page+$x)."\">".($page+$x)."</a> ";
	if ($pages-$right+1 > $page) {
		$echo .=  " ... <a href=\"$url".($pages)."\">$pages</a> ";
	if ($page < $pages) {
		$echo .=  "<a href=\"$url".($page+1)."\">Next &gt;&gt;</a>";
	$echo .= "</div>";
	if (($pages == 0 && $page<$pages) || ($page == 1 && $pages <= 1)) $echo = "";
	return $echo;
function getClause() {
	global $lim,$pg,$ITEMS_PER_PAGE;
	$pg = abs($_GET['pg']);
	if (isset($ITEMS_PER_PAGE)) {
		$ipp = $ITEMS_PER_PAGE;
	if (!is_numeric($pg) OR $pg < 1) $pg = 1;
	$lim = ($pg - 1) * $ipp + 1;
	$clause = " LIMIT " . ($lim-1) . ", ".$ipp;
	return $clause;

// let's assume you have an SQL() function to handle your mysql queries:

setQueries(Sql('SELECT COUNT(*) as ctr FROM contacts')); // this is important so the scripts knows when to end
$res = Sql('SELECT * FROM contacts' . getClause());
while ($row = mysql_fetch_array($res)) {
	echo '<br />';
echo makePages();

Open in new window


Author Closing Comment

ID: 34900228
Hey thanks everyone.  After going through all of the suggestions, the one that worked out best for me was the Perfect Pagination tutorial.  The sample code made it really easy to follow while learning, rather than just pasting in a solution I didn't understand.  I am very thankful for all the input and I'm sure I'll be back with more exciting newbie challenges before long!
LVL 109

Expert Comment

by:Ray Paseur
ID: 34900282
Thanks for the points.  I've always found SitePoint to be a great resource. ~Ray

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question