SQL SELECT statement queries can always result in thousands of records. But it's not a good idea to display all the results on one page. Therefore, we can divide the results into multiple pages according to the requirements. Paging means displaying your query results on multiple pages, rather than just placing them all on a long page. MySQL helps generate pages by using the LIMIT clause, which takes two parameters. The first parameter is OFFSET, and the second parameter is how many records should be returned from the database. The following is a simple example of using the LIMIT clause to capture records to generate paging.
<html> <head> <title>PHP paging</title> </head> <body> <?php $dbhost = 'localhost'; // Database Host $dbuser = 'root'; // User name $dbpass = '123456'; // Password $rec_limit = 10; // 10 data per page $conn = mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die('connection failed: ' . mysqli_error()); } mysqli_select_db($conn,'test'); // Data to be manipulated /* Get all the records */ $sql = "SELECT COUNT(id) FROM test "; $retval = mysqli_query( $conn, $sql ); if(! $retval ) { die('No data was obtained: ' . mysqli_error($conn)); } $row = mysqli_fetch_array($retval, MYSQLI_NUM ); $rec_count = $row[0]; if( isset($_GET['page'] ) ) { $page = $_GET['page'] + 1; $offset = $rec_limit * $page ; }else { $page = 0; $offset = 0; } $left_rec = $rec_count - ($page * $rec_limit); $sql = "SELECT name ". "FROM test ". "LIMIT $offset, $rec_limit"; $retval = mysqli_query( $conn, $sql ); if(! $retval ) { die('Failure to obtain data: ' . mysqli_error($conn)); } while($row = mysqli_fetch_array($retval, MYSQLI_ASSOC)) { echo "TEST name :{$row['name']} <br> "; } if( $page > 0 ) { $last = $page - 2; echo "<a href = \"".$_SERVER['PHP_SELF']."?page=$last\">Previous page</a> |"; echo "<a href = \"".$_SERVER['PHP_SELF']."?page=$page\">next page</a>"; }else if( $page == 0 ) { echo "<a href = \"".$_SERVER['PHP_SELF']."?page=$page\">next page</a>"; }else if( $left_rec < $rec_limit ) { $last = $page - 2; echo "<a href = \"".$_SERVER['PHP_SELF']."?page=$last\">last page</a>"; } mysqli_close($conn); ?> </body> </html>
Modify the data displayed on each page or insert multiple data to see the effect. PHP MySQL inserts data.