Home >>MySQL Tutorial >PHP MySQL Pagination

PHP MySQL Pagination

How to use Pagination in PHP MySQL

MySQL SELECT query may display the thousands of records from the database. TO show these thousands of records in a single page is not possible. for that purpose we use the concept of MySQL pagination. In Mysql pagination we divide results into many pages.Basically it is the method of showing somewhat lesser results on page instead of putting them all together on a single page. Suppose there are 15 records in a table. 5 records on each page are displayed by using pagination. each of the 3 pages will have 5 records.Each time we click on next page the next 5 records will got displayed and the previous 5 records will get removed. Below is the Example. In above example there is paginate function which has two arguments , one is the connection of database and other is Limit variable which is basically the number of records on each page. FOUND_ROWS() function return the number of rows from the table in the absence of limit clause.

Show 5 records on each page

There are 15 records inside userinfo table. Show only 5 records on each page.(use Pagination) save as paginate.php

<?php

function paginage($db,$limit)
{

$sql ='select FOUND_ROWS();';

$result=mysql_query($sql);

$row=mysql_fetch_array($result);

$numrows=$row[0];

$paglinks='<p>';

if($numrows > $limit)

{

$page=$_GET['page']?$_GET['page']:1; 

//set page click_page
$currentpage=$_SERVER['PHP_SELF'].'?'.$_SERVER['QUERY_STRING'];

$currentpage=str_replace('&page='.$page,'',$currentpage);

//now if page_click on 1
if($page==1)

{

$paglinks.='<PREV';

}

else
{

$page_pre=$page-1;

$paglinks.='<a href="'.$currentpage.'&page='.$page_pre.'
"><PREV</a>';

}

//number of pages and range(no of links show)
$numofpages=ceil($numrows/$limit);

$range=4;

//left range and right range
$l_range=max(1,$page-(($range-1)/2));

$r_range=min($numofpages,$page+(($range-1)/2));

if(($r_range-$l_range)< $range-1)

{

if($l_range==1)

{

$r_range=min($l_range + ($range-1),$numofpages);

}

else

{

$l_range=max($r_range-($range-1),0);

}

}

$paglinks.=($l_range>1)?'....':'  ';

for($i=1; $i<$numofpages; $i++)//for loop this make the numric page link show

{

if($i==$page)

{

$paglinks.=$i;

}

else

{

if($l_range<= $i and $i<=$r_range)

{

$paglinks.='<a href="'.$currentpage.'&page='.$i.'">['.$i.']</a>';

}

}

}

$paglinks.=($r_range< $numofpages)?'....':'  ';

if($numrows-($limit * $page)>0)

{

$pagenext=$page +1;

$paglinks .=	'<a href="'.$currentpage.'&page='.$pagenext.'">  NEXT></a>';

}

else

{

$paglinks .='NEXT>';

}

}

else

{

$paglinks.='<PRVE   NEXT> ';

}

$paglinks.='</p>';

return $paglinks;

}		 

?>

Select records from userinfo table(Total records are 12). show 5 records on each page.

<?php

include("paginate.php");

$conn=mysql_connect("localhost","root");

$db=mysql_select_db("test",$conn);

error_reporting(1);

?>

<body>

<table border="1"  class="list" cellpadding="4px" >

<tr>

<td>SN</td>

<td>Name</td>

<td>Email</td>

<td>Mobile</td>

</tr>

<?php

$page=$_GET['page']?$_GET['page']:1;

$limit=5;

$start=($page-1)*$limit; 

$query4='select SQL_CALC_FOUND_ROWS * from userinfo limit '.$start.','.$limit;

$result=mysql_query($query4);

$page_links=paginage($db,$limit);

$num=1;

while($row=mysql_fetch_array($result))
{

echo'<tr>';

echo'<td>'.$row['id'].'</td>';

echo'<td>'.$row['name'].'</td>';

echo'<td>'.$row['email'].'</td>';

echo'<td>'.$row['mobile'].'</td>';

$num++;

echo'</tr>';

}

?>

<tr>

<td colspan="6" align="center"><?php echo $page_links; ?></td>

</tr>

</table>					

</body>

</html>

In display.php we have included the paginate.php and the results fetched by it are shown in a tabular form. The records in the result variable are fetched by mysql_fetch_array() function which is put in a while loop to display all records. The information fetched by the display function is subdivided into pages and each page contain maximum of 5 records.

« Previous Next »

MySQL Tutorial SQL Introduction MySQL Constraints MySQL Common Queries PHP MySQL Connectivity PHP Mysqli Database Connectivity PHP MySQL Create Database PHP Create MySQLi Database PHP MySQL Insert PHP MySQL Update PHP MySQL Delete PHP MySQL Select Mysqli Fetch Array Mysqli Fetch Assoc Mysqli fetch row Mysqli fetch object MySQL Where Clause MySQL Order By Select Insert Update Delete in Php How to create foreign key MySQL auto increment PHP MySQL Secure Registration Page PHP MySQL Secure Login Page MySQL Predefined Function MySQL Joins How to Export MySQL to CSV How to import csv into mysql How to edit radio button in PHP MySQL PHP MySQL Pagination Upload and Display Video in PHP Building a bar chart How to update Radio button, Checkbox and dropdown Create Registration form PHP MySQL How to insert data from textarea to database PHP PHP PDO

No Sidebar ads