PHP MySQL and Pagination
From w3cyberlearnings
Contents |
PHP pagination function
- Design a pagination generic function for all type of database drivers.
page nation script function
- $totalcount: Total Records
- $page: current page (first page start with 0)
- $perpage: display record per page
- $baseurl: the URL (/page.php')
- $pagevar: assign the variable for the URL (/page.php?page=..)
- $nocurr: false
- $return: return or echo (when set to true it will return so that we can assign to another variable)
- $maxdisplay: the maximum display for records
function mypaging_bar(
$totalcount, $page, $perpage, $baseurl, $pagevar='page', $nocurr=false, $return=false, $maxdisplay=18) {
$output = '';
if ($totalcount > $perpage) {
$output .= '<div class="paging">';
$output .= 'page' . ':';
if ($page > 0) {
$pagenum = $page - 1;
$output .= ' (<a class="previous" href="'
. $baseurl
. $pagevar
. '=' . $pagenum
. '">'
. 'previous' . '</a>) ';
}
if ($perpage > 0) {
$lastpage = ceil($totalcount / $perpage);
} else {
$lastpage = 1;
}
if ($page > $maxdisplay - 3) {
$startpage = $page - floor($maxdisplay / 2);
$output .= ' <a href="' . $baseurl . $pagevar
. '=0">1</a> ...';
} else {
$startpage = 0;
}
$currpage = $startpage;
$displaycount = $displaypage = 0;
while ($displaycount < $maxdisplay and $currpage < $lastpage) {
$displaypage = $currpage + 1;
if ($page == $currpage && empty($nocurr)) {
$output .= ' ' . $displaypage;
} else {
$output .= ' <a href="' . $baseurl . $pagevar
. '=' . $currpage . '">' . $displaypage . '</a>';
}
$displaycount++;
$currpage++;
}
if ($currpage < $lastpage) {
$lastpageactual = $lastpage - 1;
$output .= ' ...<a href="' . $baseurl . $pagevar . '='
. $lastpageactual . '">' . $lastpage . '</a> ';
}
$pagenum = $page + 1;
if ($pagenum != $displaypage) {
$output .= ' (<a class="next" href="' . $baseurl
. $pagevar . '=' . $pagenum . '">' . 'next' . '</a>)';
}
$output .= '</div>';
}
if ($return) {
return $output;
}
echo $output;
return true;
}
Usage Example 1
$totalcounts = 200; $page = 1; $perpage = 5; $baseurl = 'content.php?act=usesrs&'; $pagevar = 'page'; $nocurr = false; mypaging_bar($totalcounts, $page, $perpage, $baseurl, $pagevar = 'page', $nocurr = false);
Display Result
Usage Example 2
- We assign the return pagination function to the $mypagination variable.
$totalcounts = 200; $page = 1; $perpage = 5; $baseurl = 'content.php?act=usesrs&'; $pagevar = 'page'; $nocurr = false; $return = false; $mypagination= mypaging_bar($totalcounts, $page, $perpage, $baseurl, $pagevar = 'page', $nocurr = false, $return=true); echo $mypagination;
Display Result
HTML Generate for the Pagination
<div class="paging"> page: (<a class="previous" href="content.php?act=usesrs&page=0">previous</a>) <a href="content.php?act=usesrs&page=0">1</a> 2 <a href="content.php?act=usesrs&page=2">3</a> <a href="content.php?act=usesrs&page=3">4</a> <a href="content.php?act=usesrs&page=4">5</a> <a href="content.php?act=usesrs&page=5">6</a> <a href="content.php?act=usesrs&page=6">7</a> <a href="content.php?act=usesrs&page=7">8</a> <a href="content.php?act=usesrs&page=8">9</a> <a href="content.php?act=usesrs&page=9">10</a> <a href="content.php?act=usesrs&page=10">11</a> <a href="content.php?act=usesrs&page=11">12</a> <a href="content.php?act=usesrs&page=12">13</a> <a href="content.php?act=usesrs&page=13">14</a> <a href="content.php?act=usesrs&page=14">15</a> <a href="content.php?act=usesrs&page=15">16</a> <a href="content.php?act=usesrs&page=16">17</a> <a href="content.php?act=usesrs&page=17">18</a> ...<a href="content.php?act=usesrs&page=39">40</a> (<a class="next" href="content.php?act=usesrs&page=2">next</a>) </div>
Create Table and Insert Sample Records
Create table myorder(
id int not null auto_increment primary key,
item varchar(200) not null,
customer_name varchar(200) not null,
address varchar(200) not null,
price double not null
);
INSERT INTO myorder (item,customer_name,address,price)
VALUES('Java','Bob Maat','2450 Jame Dr, Houston,Tx',30),
('PHP/MySQL','Paul Chao','3400 Lake Rd, Houston,Tx',40),
('MySQL/Ajax','David','3203 Maka, Houston, Tx',39),
('MySQL 5','Paul David','3203 Franklin Dr, Houston, Tx',39),
('Postgresql','Lili','430 Mimin, Ruston, LA',32),
('PHP/Postgresql','Jamey','7320 CA LA, LA, CA',30),
('Perl','Chris d','3203 kilo, Houston, Tx',49),
('jQuery','Maki','3320, Houston, Tx',20),
('HTML/jQuery','Likinu','320, CA, FL',22),
('PHP/jQuery','Maki','3320, Houston, Tx',25),
('Mobile/jQuery','Maki','778, Houston, Tx',23),
('CSS/jQuery','Maki','3320, Houston, Tx',42),
('C++','LiMaki','3320, Houston, Tx',50),
('java 5','LOMaki','3320, Houston, Tx',20),
('Jscript','Maki','3320, Houston, Tx',40),
('Linux','Maki','3320, Houston, Tx',30),
('Mobile Design','Maki','3320, Houston, Tx',42),
('Android/AJax','Maki','3320, Houston, Tx',60),
('Webtop/EMCS','Maki','3320, Houston, Tx',70),
('Documentum/Xcp','Maki','3320, Houston, Tx',120),
('Javascript/JQuery','Maki','3320, Houston, Tx',20);
Pagination with ADODB5 driver for MySQL and PHP
Connect to the MySQL database
<?php
require_once ('adodb.inc.php');
$host = 'localhost';
$user = 'root';
$pass = 'caojiang';
$dbname = 'school';
// connect to the database using adodb5
$conn1 = &ADONewConnection('mysql');
$conn1->PConnect($host, $user, $pass, $dbname);
//$conn1->debug = true;
Define variables
$totalcounts = 0; //get request for each click for next and previous page $page = (!empty($_REQUEST['page']) ? $_REQUEST['page'] : 0); $perpage = 5; $baseurl = 'mypagination.php?'; $pagevar = 'page'; $nocurr = false; $return = false;
Count the total records
// Get and count all the records
$sql = "SELECT COUNT(*) AS total FROM myorder";
$rs = $conn1->Execute($sql);
if (is_object($rs) and is_array($rs->fields)) {
$totalcounts = reset($rs->fields);
}
Generate page and display Result
- We only generate and display records when the total record is larger than per page.
if ($totalcounts > $perpage) {
// display pagination
mypaging_bar($totalcounts, $page, $perpage, $baseurl, $pagevar = 'page', $nocurr = false);
// get the record
$sql = "SELECT item, customer_name, address,price FROM myorder";
$rs = $conn1->SelectLimit($sql, $perpage, $page);
echo '<table border="1" cellspacing="8" cellpadding="8">';
echo '<tr><td>Item Name</td><td>Customer Name</td><td>Address</td><td>Price</td></tr>';
while (!$rs->EOF) {
echo '<tr><td>' . $rs->fields[0] . '</td><td>' .
$rs->fields[1] . '</td><td>' . $rs->fields[2] . '</td><td>' . $rs->fields[3] . '</td></tr>';
$rs->MoveNext();
}
echo '</table>';
mypaging_bar($totalcounts, $page, $perpage, $baseurl, $pagevar = 'page', $nocurr = false);
}
Completed Code
<?php
// add the pagination function
require_once 'mypagination_bar.php';
require_once ('adodb.inc.php');
$host = 'localhost';
$user = 'root';
$pass = 'caojiang';
$dbname = 'school';
// connect to the database using adodb5
$conn1 = &ADONewConnection('mysql');
$conn1->PConnect($host, $user, $pass, $dbname);
//$conn1->debug = true;
$totalcounts = 0;
$page = (!empty($_REQUEST['page']) ? $_REQUEST['page'] : 0);
$perpage = 5;
$baseurl = 'mypagination.php?';
$pagevar = 'page';
$nocurr = false;
$return = false;
// Get and count all the records
$sql = "SELECT COUNT(*) AS total FROM myorder";
$rs = $conn1->Execute($sql);
if (is_object($rs) and is_array($rs->fields)) {
$totalcounts = reset($rs->fields);
}
// finished count records
if ($totalcounts > $perpage) {
// display pagination
mypaging_bar($totalcounts, $page, $perpage, $baseurl, $pagevar = 'page', $nocurr = false);
// get the record
$sql = "SELECT item, customer_name, address,price FROM myorder";
$rs = $conn1->SelectLimit($sql, $perpage, $page);
echo '<table border="1" cellspacing="8" cellpadding="8">';
echo '<tr><td>Item Name</td><td>Customer Name</td><td>Address</td><td>Price</td></tr>';
while (!$rs->EOF) {
echo '<tr><td>' . $rs->fields[0] . '</td><td>' . $rs->fields[1]
. '</td><td>' . $rs->fields[2] . '</td><td>' . $rs->fields[3] . '</td></tr>';
$rs->MoveNext();
}
echo '</table>';
mypaging_bar($totalcounts, $page, $perpage, $baseurl, $pagevar = 'page', $nocurr = false);
}
?>
Display Result 1
Display Result 2
Display Result 3
