Paging MySQL results with PHP

Question

How would you split MySQL results into 'next' and 'previous' if the output was very long?

$data = mysql_query("SELECT * FROM table WHERE ... AND ...")  
  or die(mysql_error());

while($info = mysql_fetch_array( $data ))
{
  echo $info['value'];
}

Where values would be listed from 1 to 10 and then go to next? The first page could be done with LIMIT 10 but how would I do the following ones?

Answer 1

SELECT * FROM table WHERE ... AND ... LIMIT 10 OFFSET 10

I prefer having the OFFSET word just because it's a bit more readable.

Also remember that the offset is 0 based (the offset of the first row returned is 0, not 1). So LIMIT 10 OFFSET 10 will return rows 10 to 19.

Answer 2

You can specify an offset in the LIMIT clause:

LIMIT 0, 10
LIMIT 10, 10
LIMIT 20, 10
...

So when building the LIMIT clause your code should look like that:

$offset = ($current_page - 1) * $items_per_page;
$sql = "[...] LIMIT $offset, $items_per_page";

Of course you'll have to ensure that $current_page is >= 1. But that's easily done:

$current_page = empty($_GET['current_page']) ? 1 : max(1, (int)$_GET['current_page']);