Php Sql Paging OR Pagination
This application illustrates how to create a pagination in php.
Some times the programmer needs to display a large amount of data. Displaying all data in one page in not a good idea. For this situation, pagination is the solution. Dividing the result into many pages is known as pagination. In this example, we have to display maximum 10 rows per page. According to the total no of rows and maximum no of rows per page, the result is displayed in many pages. Links to navigate from one page to the other has been created at bottom.
Table: employee
In the example,
Total rows: 19
Maximum rows per page:10
From the above data, we come to the result that there will be 2 pages.
Source Code of php_paging.php
<html>
<head>
<title>PHP Pagination Example</title>
</head>
<body>
<div style="width:500px">
<?php
define('MAX_REC_PER_PAGE', 10);
$db = mysql_connect("localhost", "root", "root") or die("Couldn't connect to db!");
mysql_select_db("test") or die("Couldn't select db!");
$rs = mysql_query("SELECT COUNT(*) FROM employee") or die("Count query error!");
list($total) = mysql_fetch_row($rs);
$total_pages = ceil($total / MAX_REC_PER_PAGE);
$page = intval(@$_GET["page"]);
if (0 == $page){
$page = 1;
}
$start = MAX_REC_PER_PAGE * ($page - 1);
$max = MAX_REC_PER_PAGE;
$rs = mysql_query("SELECT name, designation, salary FROM employee ORDER BY salary
ASC LIMIT $start, $max") or die("Employee query error!");
?>
<table border="1" width="100%">
<tr>
<th>Name</th>
<th>Designation</th>
<th>Salary</th>
</tr>
<?php
while (list($name, $designation, $salary) = mysql_fetch_row($rs)) {
?>
<tr>
<td width="30%"><?= htmlspecialchars($name) ?></td>
<td><?= htmlspecialchars($designation) ?></td>
<td><?= htmlspecialchars($salary) ?></td>
</tr>
<?php
}
?>
</table>
<table border="0" cellpadding="5" align="center">
<tr>
<td>Goto Page:</td>
<?php
for ($i = 1; $i <= $total_pages; $i++) {
$txt = $i;
if ($page != $i)
$txt = "<a href=\"" . $_SERVER["PHP_SELF"] . "?page=$i\">$txt</a>";
?>
<td align="center"><?= $txt ?></td>
<?php
}
?>
</tr>
</table>
<hr>
</div>
</body>
</html>
Output:
