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:

  1. we print next 10 results by getting s parameter for next 10 set of results.
  2. 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 :

  1. we print next 10 results by getting s parameter for next 10 set of results by breaking loop
  2. 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