Pagination of MySQL Query Results
As your database grows, showing all the results of a query on a single page is no longer practical. This is where pagination comes in handy. You can display your results over a number of pages, each linked to the next, to allow your users to browse your content in bite sized pieces. In the following example we use pagination with the name of countries.
First
IF loop checks if any link is clicked or not (for next results), and the results displayed thereafter as:
- we print next 10 results by getting s parameter for next 10 set of results.
- then we list all links for whole table in the gap of 10, by incrementing for loop to 10+ steps each.
Second
ELSE block comes when there is no link is clicked and the page is first 10 results :
- we print next 10 results by getting s parameter for next 10 set of results by breaking loop
- then we list all links for whole table in the gap of 10, by incrementing for loop to 10+ steps each.
Here the important thing other than logic is MySql syntax to display next rows from starting nth row.
SELECT * FROM nameofcountries LIMIT 200 , 10 # EXPLANATION # SELECT <columns> FROM <table> LIMIT <starting number of row> , <next n results>
Example
<!DOCTYPE HTML>
<html>
<head>
<title>Pagination</title>
<style>
/* STYLE FOR TD BORDER */
td
{
border: 1px solid #F00;
}
</style>
</head>
<body>
<?php
// connecting
$con = mysql_connect ( 'localhost', 'username', 'password' );
if (! $con)
{
die ( 'Could not connect: ' . mysql_error () );
}
mysql_select_db ( "test", $con );
// CHECK IF ANY LINK IS CLICKED OR NOT, ON PAGINATION
// IF ANY LINK IS NOT CLICKED THEN PAGE MUST BE FIRST PAGE
if (isset ( $_GET ['s'] ))
{
$var = ( int ) $_GET ['s']; // typecasting to int
// limit to present row to + 10
$sql = "SELECT * FROM nameofcountries limit " . $var . "," . 10;
$result = mysql_query ( $sql );
$rows = mysql_num_rows ( $result );
// display first 10 results
for($j = 0; $j < $rows; ++ $j) {
echo "Number : " . mysql_result ( $result, $j, 'Number' ) .
", ID : " . mysql_result ( $result, $j, 'ID' ) . ", Name : "
. mysql_result ( $result, $j, 'Name' ) . '<br />';
}
$sql = "SELECT * FROM nameofcountries";
$result = mysql_query ( $sql );
if (! $result)
die ( "Database access failed: " . mysql_error () );
$rows = mysql_num_rows ( $result );
?>
<!-- START PAGINATION TABLE -->
<table>
<tr>
<?php
// LOOOP IS DISPLAYED FOR ALL RESULTS IN GAP OF 10
for($j = 0; $j < $rows; $j = $j + 10)
{
?>
<td><a href="index.php?s=<?php echo $j ?>"><?php echo $j ?></a></td>
<?php
}
?>
</tr>
</table>
<!-- ENDING PAGINATION TABLE -->
<?php
} // ENDING IF BLOCK
else // THIS IS FOR FIRST PAGE
{
$sql = "SELECT * FROM nameofcountries";
$result = mysql_query ( $sql );
if (! $result)
die ( "Database access failed: " . mysql_error () );
$rows = mysql_num_rows ( $result );
for($j = 0; $j < $rows; ++ $j)
{
if ($j == 10) // IF 10 RECORDS DISPLAYED, BREAK LOOP
break;
echo "Number : " . mysql_result ( $result, $j, 'Number' ) .
", ID : " . mysql_result ( $result, $j, 'ID' ) . ", Name : " .
mysql_result ( $result, $j, 'Name' ) . '<br />';
}
?>
<!-- START PAGINATION TABLE -->
<table>
<tr>
<?php
for($j = 0; $j < $rows; $j = $j + 10)
{
?>
<td><a href="index.php?s=<?php echo $j ?>"><?php echo $j ?></a></td>
<?php
}
?>
</tr>
</table>
<!-- ENDING PAGINATION TABLE -->
<?php } ?>
</body>
</html>
Output
