Here this script is for splitting MySQL query result into multiple pages/dynamic paging.
CSS is provided for sample style only.
# No. indicates changes needed or you would like to change. It will show 2 rows per page only. Change $recordsPerPage
for more records per pages.
Change at marked places #1, #2 and #3 is must in case your table structure is different, which is most likely.
Here and there commented code are left for some additional information.
<style type="text/css" >
table.data {
background-color:#fff;
color:#213243;
}
.data th {background-color:gray; color:#ffffff}
.pagingDiv { width:270px; height:20px}
.pNo {float:left; width:30px; margin-left:3px; margin-right:3px; border:0px solid gray;}
.pNo a {text-decoration:none;}
</style>
<?php
/*
Suppose my table has two columns.
name (varchar 100) and date (date).
CREATE TABLE `tblfour` (
`name` varchar(20) NOT NULL default '',
`date` date NOT NULL default '0000-00-00',
KEY `name` (`name`)
) ENGINE=MyISAM
*/
require 'database connection file'; // use file for db conn or use below codes.
/*
// Database connection
$host = 'localhost';
$user = 'username';
$pwd = 'pwdhere';
$dbName = 'dbName';
$conn = mysql_connect ($host, $user, $pwd) or die ('Connection Failed.<br>');
$db = mysql_select_db ($dbName) or die ('DB selection failed.');
*/
// Number of records to show per page
$recordsPerPage = 2; # 0
// default startup page
$pageNum = 1;
if(isset($_GET['p'])) {
$pageNum = $_GET['p'];
settype($pageNum, 'integer');
}
$offset = ($pageNum - 1) * $recordsPerPage;
$query = "SELECT name, date FROM tblfour WHERE name='a' LIMIT $offset, $recordsPerPage;"; # 1. Main query
$result = mysql_query($query) or die('Mysql Err. 1');
// print table
$data = '<table class="data" border="1" cellpadding="3" cellspacing="0" rules="rows" frame="below">
<tr>
<th>Name</th>
<th>Date</th>
</tr>
';
# 2 change/add columns name
while($row = mysql_fetch_assoc($result)) {
$data .= "n<tr>
nt<td> $row[name]</td>
nt<td> $row[date] </td>
n</tr>
";
}
$data .= "n</table>n";
# Update this query with same where clause you are using above.
$query = "SELECT COUNT(date) AS dt FROM tblfour WHERE name='a';"; # 3
$result = mysql_query($query) or die('Mysql Err. 2');
$row = mysql_fetch_assoc($result);
$numrows = $row['dt']; # 4
$maxPage = ceil($numrows/$recordsPerPage);
$nav = '';
for($page = 1; $page <= $maxPage; $page++)
{
if ($page == $pageNum)
{
$nav .= "<div class="pNo">$page</div>";
}
else
{
$nav .= "<div class="pNo"><a href="?p=$page">$page</a></div>";
}
}
if ($pageNum > 1) {
$page = $pageNum - 1;
$prev = "<a href="?p=$page"><strong><</strong></a>";
$first = "<a href="?p=1"><strong><<</strong></a>";
}
else {
$prev = '<strong> </strong>';
$first = '<strong> </strong>';
}
if ($pageNum < $maxPage) {
$page = $pageNum + 1;
$next = "<a href="?p=$page"><strong>></strong></a>";
$last = "<a href="?p=$maxPage"><strong>>></strong></a>";
}
else {
$next = '<strong> </strong>';
$last = '<strong> </strong>';
}
Everything is ready for paging, just output to browser:
echo $data;
echo '<br />';
echo "<div class="pagingDiv">
<div class="pNo">$first</div>
<div class="pNo">$prev</div>
$nav
<div class="pNo">$next</div>
<div class="pNo">$last</div></div>";
?>
Now, you know how to implement paging. "Paging implemented in Ajax" is using almost same PHP code with Ajax facility.
# 1 - by Barlymasher
Worked very well for me, thanks. Now I just need to figure out how to do this with being able to sort on a column as well.