from the Artful MySQL Tips List

Paginating a MySQL query result in PHP is just a matter of connecting to the database server, then looping through the following steps each time the user asks for more data:
  • if it's the first instance of the loop, set offset=0, otherwise adjust the offset value according to whether the user clicked the Top, Previous, Next or Bottom button,
  • issue the query with a LIMIT clause incorporating that offset,
  • retrieve column headers and paint them,
  • retrieve the query result and paint the data rows,
  • configure and paint Top, Previous, Next and Bottom command buttons according to the current offset, and when the user clicks on one, POST back to the page the current offset value and the identity of the clicked button.
The following script does that for any table of any size. Beware, though, that with more than a few hundred thousand rows, retrieval can be slow:

 * paginate.php

if( empty( 
$db ) || empty( $tbl ) || empty( $host ) || empty( $usr ) || empty( $pwd ) || empty( $pgsize ))
"Missing database, table, hostname, username, password or page size setting" );

$conn mysql_connect$host$usr$pwd ) or exit( mysql_error() );
mysql_select_db$db ) or exit( mysql_error() );

$sql "select count(*) from (select 1 from $db.$tbl) tmp";
$result mysql_query$sql 
          or exit( 
"Query $sql elicited this error:<br/>" mysql_error() );
$total mysql_result$result);
if( isset( 
$_POST['offset'] )) {
$_POST['submit'] == "Top" )
$offset 0;
$_POST['submit'] == "Next" )
$offset min$total $pgsize 1$_POST['offset'] + $pgsize ) ;
$_POST['submit'] == "Previous" 
$offset max0$_POST['offset'] - $pgsize );
$_POST['submit'] == "Bottom" )
$offset max$total $pgsize 10);

$display "<h4>Pagination of $total $db.$tbl rows Offset=$offset Rows per page=$pgsize</h4>\n";

$sql "SELECT * FROM $db.$tbl ORDER by 1 LIMIT $offset$pgsize";
$result mysql_query$sql 
          or exit( 
"Query $sql elicited this error:<br/>" mysql_error() );
$rows mysql_num_rows$result ) or exit( mysql_error() );

$display .= "<table border='1'><thead>\n";
$cols mysql_num_fields$result ) or exit( mysql_error() );
$i=0$i $cols$i++ ) {
$col mysql_fetch_field$result );
$display .= "<th>{$col->name}</th>\n";
$display .= "</thead>\n";

for( $i=0$i $rows$i++ ){
$row mysql_fetch_row$result );
$display .= "<tr>\n";
$row as $cell 
$display .= "<td>" . (( empty( $cell )) ? "&nbsp;" $cell ) . "</td>\n";
$display .= "</tr>\n";
$display .= "</tbody></table>\n";

$top_disabled =  ( $offset <= $pgsize ) ? "disabled" "";
$prev_disabled = ( $offset <= $pgsize ) ? "disabled" "";
$next_disabled = ( $offset >= $total $pgsize ) ? "disabled" "";
$bott_disabled = ( $offset >= $total $pgsize ) ? "disabled" "";
$top      "<input type='submit' name='submit' value='Top' $top_disabled>";
$previous "<input type='submit' name='submit' value='Previous' $prev_disabled>";
$next     "<input type='submit' name='submit' value='Next' $next_disabled>";
$bott     "<input type='submit' name='submit' value='Bottom' $bott_disabled>";
$display .= "<br /><form name='form1' action='{$_SERVER['PHP_SELF']}' method='post'>" .
"<input type='hidden' name='offset' value='$offset'>" .
"$top &nbsp; $previous &nbsp; $next &nbsp; $bott<br/>" .

<body><?php echo $display; ?></body>

With millions of rows, translate the paginating LIMIT arguments into WHERE clause arguments.

Return to the Artful MySQL Tips page