Link to home
Create AccountLog in
Avatar of nikaotech

asked on

PHP / MySQL Pagination

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

Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

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

Oops, extra quote above.
$result = mysql_query("SELECT * FROM contacts LIMIT $cpos,20", $connection);

Open in new window

Avatar of nikaotech


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...
You have to give "$cpos" a value and add the logic/arithmetic to create 'prev' and 'next' links yourself.
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!

Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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

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!
Thanks for the points.  I've always found SitePoint to be a great resource. ~Ray