Its always possible that your SQL SELECT statement query may result into thousand of records. But its is not good idea to display all the results on one page. So we can divide this result into many pages as per requirement.
Paging means showing your query result in multiple pages instead of just put them all in one long page.
MySQL helps to generate paging by using LIMIT clause which will take two arguments. First argument as OFFSET and second argument how many records should be returned from the database.
Below is a simple example to fetch records using LIMIT clause to generate paging.
Example
Try out following example to display 3 records per page.
<!DOCTYPE html>
<html>
<head>
<title>Using Paging through PHP</title>
</head>
<body>
<table width="530" height="103" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<th width="50" height="38" bgcolor="#E3E3E3" scope="col">emp_id</th>
<th width="200" bgcolor="#E3E3E3" scope="col">emp_name</th>
<th width="400" bgcolor="#E3E3E3" scope="col">emp_address</th>
<th width="150" bgcolor="#E3E3E3" scope="col">emp_salary</th>
</tr>
<?php
$link=MySQL_connect('localhost','root','');
mysql_select_db('test_db');
mysql_query('set names utf-8');
$Page_size=3;
$result=mysql_query('select * from employee');
$count = mysql_num_rows($result);
$page_count = ceil($count/$Page_size);
$init=1;
$max_p=$page_count;
$pages=$page_count;
if(empty($_GET['page'])||$_GET['page']<0){
$page=1;
}else {
$page=$_GET['page'];
}
$offset=$Page_size*($page-1);
$sql="select * from employee limit $offset,$Page_size";
$result=mysql_query($sql,$link);
while ($row=mysql_fetch_array($result)) {
?>
<tr>
<td bgcolor="#E0EEE0" height="25px"><div align="center">
<?php echo $row['emp_id']?>
</div></td>
<td bgcolor="#E0EEE"><div align="center">
<?php echo $row['emp_name']?>
</div></td> <td bgcolor="#E0EEE"><div align="center">
<?php echo $row['emp_address']?>
</div></td>
<td bgcolor="#E0EEE0" height="25px"><div align="center">
<?php echo $row['emp_salary']?>
</div></td>
</tr>
<?php
}
$page_len = ($page_len%2)?$page_len:$page_len+1;//pages number
$key='<div class="page">';
$key.="<span>$page/$pages</span> ";
if($page!=1){
$key.="<a href=\"".$_SERVER['PHP_SELF']."?page=1\">first page</a> ";
$key.="<a href=\"".$_SERVER['PHP_SELF']."?page=".($page-1)."\">previous page</a>";
}else {
$key.="first page ";
$key.="previous page";
}
for($i=$init;$i<=$max_p;$i++){
if($i==$page){
$key.=' <span>'.$i.'</span>';
} else {
$key.=" <a href=\"".$_SERVER['PHP_SELF']."?page=".$i."\">".$i."</a>";
}
}
if($page!=$pages){
$key.=" <a href=\"".$_SERVER['PHP_SELF']."?page=".($page+1)."\">next page</a> ";
$key.="<a href=\"".$_SERVER['PHP_SELF']."?page={$pages}\">last page</a>";
}else {
$key.="next page ";
$key.="last page";
}
$key.='</div>';
?>
<tr>
<td colspan="4" bgcolor="#E0EEE0"><div align="center" ><?php echo $key ?></div></td>
</tr>
</table>
</body>
</html> |