Pagination

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:

<?php
/* 
 * paginate.php
 */

// SET THESE PARAMS
$db='';
$tbl='';
$host='';
$usr='';
$pwd='';
$offset=0;
$pgsize=10;
if( empty( 
$db ) || empty( $tbl ) || empty( $host ) || empty( $usr ) || empty( $pwd ) || empty( $pgsize ))
  exit( 
"Missing database, table, hostname, username, password or page size setting" );

// CONNECT, SELECT DB
$conn mysql_connect$host$usr$pwd ) or exit( mysql_error() );
mysql_select_db$db ) or exit( mysql_error() );

// PROCESS USER'S TOP/PREV/NEXT/BOTT CMD IF ANY, SET OFFSET
$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'] )) {
  if( 
$_POST['submit'] == "Top" )
    
$offset 0;
  elseif( 
$_POST['submit'] == "Next" )
    
$offset min$total $pgsize 1$_POST['offset'] + $pgsize ) ;
  elseif( 
$_POST['submit'] == "Previous" 
    
$offset max0$_POST['offset'] - $pgsize );
  elseif( 
$_POST['submit'] == "Bottom" )
    
$offset max$total $pgsize 10);
}   

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

// RETRIEVE PAGE OF DATA, COUNT ROWS RETRIEVED
$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() );

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

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

// CONFIGURE MOVEMENT BUTTONS
$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/>" .
            
"</form>\n";
?>


<html>
<head><title>Pagination</title></head>
<body><?php echo $display; ?></body>
</html>

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

Return to the Artful MySQL Tips page