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, 0 );

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 = max( 0, $_POST['offset'] - $pgsize );

  elseif( $_POST['submit'] == "Bottom" )

    $offset = max( $total - $pgsize + 1, 0);

}   



// 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 )) ? " " : $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   $previous   $next   $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.

Last updated 9 Dec 2024

Return to the Artful MySQL Tips page