PHP / MySQL Pagination

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

Select allOpen in new window

Oops, extra quote above.

$result = mysql_query("SELECT * FROM contacts LIMIT $cpos,20", $connection);

Select allOpen in new window

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...

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

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!

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();
?>

Select allOpen 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