What is a good, simple php script for paginating mysql results?

Question

I need to find a way to get results from php queries onto paged results pages. I need to know where to put the pagination script, i.e. does it go in the original script, is it all seperate, etc.?

Answer 1

Well, you need to tie together three things:

1. The current page number,
2. The number of records per page, and
3. The numbers in the LIMIT clause in your MySQL query.

So here is a simple demo script:

$perPage = 20;
if (is_numeric($_REQUEST['page'])) {
$page = (int) $_REQUEST['page'];
if ($page < 1) {
$page = 1;
}
} else {
$page = 1;
}
$start = ($page - 1) * $perPage;
$query = 'SELECT [fields] FROM [table] ' .
"WHERE [condition] LIMIT $start, $perPage";
$result = mysql_query($query);
while ($record = mysql_fetch_srray($result)) {
// display results
}
$prev = $page - 1;
$next = $page + 1;
$prevLink = $_SERVER['PHP_SELF'] . '?page=' . $prev;
$nextLink = $_SERVER['PHP_SELF'] . '?page=' . $next;
echo "

Answer 2

First of all, keep track of the current page number. You can do this in the URL (for example: results.php?page=1).

Then, when you load from the database in results.php, take note of the current page and then added a LIMIT clause to your query. For example, if you were showing 100 results per page and you were on page 2, you would start from the 100th entry instead of the 0th entry. (Remember that page 1 and "page 0" are the same). To put the list of click-able page numbers, you need to get the total number of results and find out how many pages there are.

You can do the query and the page logic in the same script. Get the page number, figure how many records to script, limit the query by using SQL's LIMIT, then display the results and the list of page number links.

There's another way to do this too instead of using page numbers. Many software programs, instead of passing along the page number (such as results.php?page=15), pass along the starting index of results (like results.php?start=100). It would start after 100 results for example.