Solved

PHP / MySQL Pagination

Posted on 2011-02-12
9
496 Views
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 lynda.com.  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%">
    <tr>
      <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>
        <p>
          <?php
	$result = mysql_query("SELECT * FROM contacts", $connection);
	if (!$result) {
		die("Database query failed: " . mysql_error());
		
	}


echo "<table border='1'>
<tr>
<th>User ID</th>
<th>User Type</th>
<th>First Name</th>
<th>Last Name</th>
<th>DateOfBirth</th>
<th>Employer</th>
<th>BackgroundCheck</th>
<th>OrientationDate</th>
<th>Comments</th>
<th>DateEntered</th>
</tr>";

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>";

?>
        </p></td>
    </tr>
  </table>
  <?php include("includes/footer.php"); ?>

Open in new window

0
Comment
Question by:nikaotech
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 82

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

0
 
LVL 82

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

0
 

Author Comment

by:nikaotech
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"...now I get 20 rows, but no prev/next...
0
 
LVL 82

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.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

http://www.tutorialized.com/view/tutorial/Pagination-with-PHP/6925

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>";
}else{
$select .= "<option value=10>10</option>";
}
if($results_per_page == 20){
$select .= "<option selected value=20>20</option>";
}else{
$select .= "<option value=20>20</option>";
}
if($results_per_page == 30){
$select .= "<option selected value=30>30</option>";
}else{
$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!

0
 
LVL 108

Accepted Solution

by:
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:
http://articles.sitepoint.com/article/perfect-php-pagination
0
 
LVL 8

Expert Comment

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

<?php
DEFINE('ITEMS_PER_PAGE',20);

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'];
	$ipp = ITEMS_PER_PAGE;
	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']);
	$ipp = ITEMS_PER_PAGE;
	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)) {
	var_dump($row);
	echo '<br />';
}
echo makePages();
?>

Open in new window

0
 

Author Closing Comment

by:nikaotech
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!
0
 
LVL 108

Expert Comment

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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
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.

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now