Recently asked on Yahoo! Answers:

Paginate in php: 3 items on front page-then 1 item on next pages?

I know how to paginate mysql results, but have only been able to have the same number of results appear on page 1 and the remainder of the clicked pages. However, I want to have a different set of results on the first page, and then on pages 2 and up, I want a different number of results. For example, on page 1, I want 3 results, then on pages 2 and up, just one result. Thanks.

I’ve covered this several times before, but never as a seperate topic, and it’s often asked: How do you paginate a PHP recordset?

It’s fairly simple, using mysql_data_seek() and a few checks to ensure our variable stays in range. We begin by assuming you will use one page to display your recordset, and will keep passing a page number parameter via querystring to that page, like this:

http://www.mysite.com/records.php?page=1

If you want to move to the second page of records, the url would be:

http://www.mysite.com/records.php?page=2

And so on. Let’s begin with the basics of PHP results set pagination; after that, the specific request.

Before I get too detailed, many other guides to PHP pagination will suggest using MySQL’s LIMIT clause, like this:

SELECT * FROM table LIMIT (X, Y)

That will work rather efficiently and is a sound choice, especially if you would otherwise return thousands of records without using LIMIT.

I generally don’t use the LIMIT method, for a couple reasons:

1. When your parameters are wildly out of range, it is far easier to recover if you have all records. That is, if someone gives too high a starting page number, it’s easier to show the first page via mysql_data_seek().
2. It is easier to output page navigation with this method and be sure that you are not going to overrun the total number of available records.

Again, if an unpaginated query would return thousands of results, use the LIMIT method. But since few queries on small sites will return that many records, generally speaking, this way is the easiest to accomplish.

Check Your Inputs

The first step is to check whether the page querystring variable is set and in range. That is, we don’t want to blindly trust that the page querystring value is always going to be an integer that is correct given the number of records we have; we want to presume someone may want to tamper with the querystring in an effort to break our script.

We also want to be prepared to handle the first visit to this page, which means the URL may not provide the page querystring variable at all.

//set page to be 1 if page variable missing or out of range
if(!isset( $_GET['page']) || !preg_match("/^[0-9]{1}$/", $_GET['page']) || $_GET['page'] < 1) {
	$page = 1;
}
else {
	$page = $_GET['page'];
}

The if statement first checks if the page querystring variable is set (that is, present in the URL). It then uses a regular expression to see if the value is a single digit, from 0 to 9. Using a regular expression prevents people sending anything other than numbers to our script.

If you wanted to make sure you could have up to 99 pages, you would change the regular expression to be /^[0-9]{1,2}$/. For more help with regular expressions, check out http://www.pcre.org/.

Finally, the script ensures our page number is at least 1. Had I used the regular expression /^[1-9]{1}$/, then I wouldn’t need this step, but I added it in case you change the regular expression; I don’t want you to forget that someone might want to send a 0 as the value of the querystring, and if you use a different regular expression, it may be that your script is vulnerable to that out-of-range attack.

If the page querystring variable isn’t present or is out of range, we default to page 1. Otherwise, we accept whatever page number was provided, at least for the moment.

Get Your Records

The next part is straighforward: Connect to the database server, select the database and run your SELECT query.

//set records per page
$PAGE_SIZE = 3;

//connect to db server, select db
$link = mysql_connect('localhost', 'user', 'pass') or die("Cannot connect to db server");
mysql_select_db('dbname') or die("Cannot select database");

//get recordset
$query = "SELECT * FROM table";
$rs = mysql_query($query) or die("Cannot parse query");

Check Your Upper Range

In addition to making sure our query provided us with at least one record, we need to ensure that the current page variable we got at the start of this script doesn’t run past the number of available records in the results set.

• We first set a variable with the number of records that were returned by our query.
• If there are no records, we echo out that fact and stop further script execution.
• If there is at least one record, we compute how many pages there should be by dividing the total number of records by the page size, and rounding any fraction up to the nearest integer.
• We then check the value of the $page variable. If it is higher than $TOTAL_PAGES, we set $page to be the value of $TOTAL_PAGES.

//get record count
$rcount = mysql_num_rows($rs);

//if no records, end script
if($rcount == 0) {
	echo "No records found";
}
else {
//get total pages in recordset
	$TOTAL_PAGES = ceil($rcount / $PAGE_SIZE);

	//check if page is in range
	if($page > $TOTAL_PAGES) {
		$page = $TOTAL_PAGES;
	}

Output Your Records

Now, for the magic. We first compute which record we should begin with by subtracting one from the page number, then multiplying that by the page size.

A results set in PHP ($rs, in our code) is really just an array of records. Like all arrays in PHP, it begins its index at 0. So, the first record in the set is at index 0 If the results set has 20 records in it, the last record is no. 19.

So, if our page size is 3, page 1 contains records 0-2; page 2 contains records 3-5; page 3 contains records 6-8; page 4 contains records 9-11; page 5 contains records 12-14; etc.

Therefore, if I want the records on page 5, I subtract 5 - 1 = 4; and multiply that by the page size, 4 * 3 = 12. Thus, I need to move to record 12 to begin outputting page 5’s records.

You can move to a specific record in a PHP results set via mysql_data_seek, which takes as arguments the results set and the record index you want.

Then, we can use a simple while loop that counts from 1 to the $PAGE_SIZE variable, outputting records so long as there are records to output.

	//determine starting record
	$start = $page - 1;
	$start = $start * $PAGE_SIZE;

	//move to starting record
	mysql_data_seek($rs, $start);

	//output records; check for end of recordset
	$i = 1;
	while($i < $PAGE_SIZE && $row = mysql_fetch_array($rs)) {
		//records are output here
		echo "$row[column]";
		$i++;
	}

Make Your Page Navigation

And that’s all there is to the pagination part. However, since we have paged records, we need a way to navigate among the pages. And that’s easy enough to do with a for loop.

	//pagination begins here
	for($i = 1; $i <= $TOTAL_PAGES; $i++) {
		if($page == $i) {
			echo "$i ";
		}
		else {
			echo "<a href=\"index.php?page=$i\">$i</a>&nbsp;";
		}
	}
}

Changing The Number Of Paged Items On Each Page

Changing the number of records per page is as simple as adding a conditional to the page size variable, resetting it to be 1 whenever we’re not on the first page.

It also requires us to recalculate the total number of pages by removing two records from the record count, since the first page will always show three records and all other pages will show 1.

To make everything clean, we declare new variables that will hold the total number of first page records, and the number of records on every other page.

We then just need to change the if-else block to recalculate page sizes and starting positions:

//if no records, end script
if( $rcount == 0) {
	echo "No records found";
}
else {
	$FIRST_PAGE_RPP = 3;
	$RPP = 1;

	if($page == 1) {
		$PAGE_SIZE = $FIRST_PAGE_RPP;
	}
	else {
		$PAGE_SIZE = $RPP;
	}
	//get total pages in recordset
	$rcount -= ($FIRST_PAGE_RPP - $RPP);
	$TOTAL_PAGES = ceil($rcount / $PAGE_SIZE);
	if($TOTAL_PAGES < 1) {
		$TOTAL_PAGES = 1;
	}

	//check if page is in range
	if( $page > $TOTAL_PAGES ) {
		$page = $TOTAL_PAGES;
	}

	//determine starting record
	$start = $page - 1;
	$start = $start * $PAGE_SIZE;
	if($page != 1) {
		$start += ($FIRST_PAGE_RPP - $RPP);
	}

	//move to starting record
	mysql_data_seek($rs, $start);

	//output records; check for end of recordset
	$i = 1;
	while($i < $PAGE_SIZE && $row = mysql_fetch_array($rs)) {
		//records are output here
		echo "$row[column]<br />";
		$i++;
	}

	//pagination begins here
	for($i = 1; $i <= $TOTAL_PAGES; $i++) {
		if( $page == $i ) {
			echo "$i ";
		}
		else {
			echo "<a href=\"index.php?page=$i\">$i</a> ";
		}
	}
}

This entry was posted on Wednesday, October 3rd, 2007 at 10:58 am PDT and is filed under PHP, Programming, SQL, Yahoo! Answers. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.