In this tutorial we will see that how to create Pagination with PHP, PDO using OOP concept, when we have number of records in database then we must Paginate the data, this is very Simple tutorial using PDO and OOP without using Ajax or jQuery it is based on pure PHP, i have created one class file using that class file you can make Pagination of database records and this class file featured with next and previous links based on the page.
Create Database & table
create a database named “dbpaging” and import the following sql code in phpmyadmin.
CREATE TABLE `dbpaging`.`tbl_tutorials` (
`tuts_id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`tuts_title` VARCHAR( 100 ) NOT NULL ,
`tuts_link` VARCHAR( 200 ) NOT NULL
) ENGINE = MYISAM ;now insert some records in the table paste the following code
INSERT INTO `dbpaging`.`tbl_tutorials` (`tuts_id`, `tuts_title`, `tuts_link`) VALUES (NULL, 'Dynamic Drop Down Menu using PHP & MySQLi', 'https://codingcage.com/2015/03/dynamic-drop-down-menu-using-php-and.html'), (NULL, 'How to use PHP Data Object - PDO tutorial', 'https://codingcage.com/2015/03/how-to-use-php-data-object-pdo-tutorial.html');
INSERT INTO `dbpaging`.`tbl_tutorials` (`tuts_id`, `tuts_title`, `tuts_link`) VALUES (NULL, 'PHP CRUD tutorial with MySQLi extension', 'https://codingcage.com/2015/03/php-crud-tutorial-with-mysqli-extension.html'), (NULL, 'Drop Down Menu using CSS3 & jQuery', 'https://codingcage.com/2015/02/how-to-create-drop-down-menu-using-css3.html');
INSERT INTO `dbpaging`.`tbl_tutorials` (`tuts_id`, `tuts_title`, `tuts_link`) VALUES (NULL, 'Server Side Form Valiations', 'https://codingcage.com/2015/02/server-side-form-validations-using-php.html'), (NULL, 'Registration & Login Script with PHP MySQl', 'https://codingcage.com/2015/01/user-registration-and-login-script-using-php-mysql.html');
INSERT INTO `dbpaging`.`tbl_tutorials` (`tuts_id`, `tuts_title`, `tuts_link`) VALUES (NULL, 'PHP CRUD with MySQL Part - 2', 'https://codingcage.com/2015/01/php-oop-crud-tutorial-with-mysql-part-2.html'), (NULL, 'Delete uploaded files from Folder', 'https://codingcage.com/2014/12/delete-uploaded-files-from-folder-in-php.html');
INSERT INTO `dbpaging`.`tbl_tutorials` (`tuts_id`, `tuts_title`, `tuts_link`) VALUES (NULL, 'Fetch data From Multiple tables', 'https://codingcage.com/2014/12/fetch-data-from-multiple-tables-in-php.html'), (NULL, 'File Upload and View with PHP & MySQl', 'https://codingcage.com/2014/12/file-upload-and-view-with-php-and-mysql.html');
INSERT INTO `dbpaging`.`tbl_tutorials` (`tuts_id`, `tuts_title`, `tuts_link`) VALUES (NULL, 'Simple PHP CRUD operations', 'https://codingcage.com/2014/12/simple-php-crud-operations-with-mysql.html'), (NULL, 'Validations', 'https://codingcage.com/search/label/validations');
INSERT INTO `dbpaging`.`tbl_tutorials` (`tuts_id`, `tuts_title`, `tuts_link`) VALUES (NULL, 'HTML5 Form validations', 'https://codingcage.com/2015/03/html5-form-validations-with-pattern.html'), (NULL, 'PHP', 'https://codingcage.com/search/label/php');Create a new file and name it ‘dbconfig.php’ and paste the following code inside it.
dbconfig.php
Set the credentials for the database and make a new PDO connection if the connection fails display the error .
Next include the ‘class.paging.php’ file and make an instance of it, pass in the database object to the class to make use of the database.
<?php
$db_host = "localhost";
$db_user = "root";
$db_pass = "";
$db_name = "dbpaging";
try
{
$DB_con = new PDO("mysql:host={$db_host};dbname={$db_name}",$db_user,$db_pass);
$DB_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $exception)
{
echo $exception->getMessage();
}
include_once 'class.paging.php';
$paginate = new paginate($DB_con);class.paging.php
this is the main class file which paginates the database records.
>> dataview() : function selects the whole records from database table.
>> paging() : function set’s the QueryString like “page_no=number”.
>> paginglink() : function creates the paging number links with “Next and Last” feature.
<?php
class paginate
{
private $db;
function __construct($DB_con)
{
$this->db = $DB_con;
}
public function dataview($query)
{
$stmt = $this->db->prepare($query);
$stmt->execute();
if($stmt->rowCount()>0)
{
while($row=$stmt->fetch(PDO::FETCH_ASSOC))
{
?>
<tr>
<td><?php echo $row['tuts_id']; ?></td>
<td><?php echo $row['tuts_title']; ?></td>
<td><a href="<?php echo $row['tuts_link']; ?>">visit</a></td>
</tr>
<?php
}
}
else
{
?>
<tr>
<td>Nothing here...</td>
</tr>
<?php
}
}
public function paging($query,$records_per_page)
{
$starting_position=0;
if(isset($_GET["page_no"]))
{
$starting_position=($_GET["page_no"]-1)*$records_per_page;
}
$query2=$query." limit $starting_position,$records_per_page";
return $query2;
}
public function paginglink($query,$records_per_page)
{
$self = $_SERVER['PHP_SELF'];
$stmt = $this->db->prepare($query);
$stmt->execute();
$total_no_of_records = $stmt->rowCount();
if($total_no_of_records > 0)
{
?><tr><td colspan="3"><?php
$total_no_of_pages=ceil($total_no_of_records/$records_per_page);
$current_page=1;
if(isset($_GET["page_no"]))
{
$current_page=$_GET["page_no"];
}
if($current_page!=1)
{
$previous =$current_page-1;
echo "<a href='".$self."?page_no=1'>First</a> ";
echo "<a href='".$self."?page_no=".$previous."'>Previous</a> ";
}
for($i=1;$i<=$total_no_of_pages;$i++)
{
if($i==$current_page)
{
echo "<strong><a href='".$self."?page_no=".$i."' style='color:red;text-decoration:none'>".$i."</a></strong> ";
}
else
{
echo "<a href='".$self."?page_no=".$i."'>".$i."</a> ";
}
}
if($current_page!=$total_no_of_pages)
{
$next=$current_page+1;
echo "<a href='".$self."?page_no=".$next."'>Next</a> ";
echo "<a href='".$self."?page_no=".$total_no_of_pages."'>Last</a> ";
}
?></td></tr><?php
}
}
}index.php
this file contains HTML and few lines of PHP code to display Paginated.
include above created ‘dbconfig.php’ in this file to use class files functions data.
you can change records per page number in this file i have set 3 .
<?php
include_once("dbconfig.php");
?>
<link rel="stylesheet" href="style.css" type="text/css" />
<table align="center" width="50%" border="1">
<tr>
<td><a href="http://cleartuts.blogspot.com/">PHP Pagination with PDO using OOP concept.</a></td>
</tr>
<tr>
<td>
<table align="center" border="1" width="100%" height="100%" id="data">
<?php
$query = "SELECT * FROM tbl_tutorials";
$records_per_page=3;
$newquery = $paginate->paging($query,$records_per_page);
$paginate->dataview($newquery);
$paginate->paginglink($query,$records_per_page);
?>
</table>
</td>
</tr>
</table>
<div id="footer">
<a href="http://cleartuts.blogspot.com/">cleartuts.blogspot.com</a>
</div>style.css
@charset "utf-8";
/* CSS Document */
table,td
{
padding:15px;
border-collapse:collapse;
}
#data td
{
padding:10px;
border-collapse:collapse;
position:relative;
font-family:Verdana, Geneva, sans-serif;
}
a
{
text-decoration:none;
color:#00a2d1;
font-family:"Courier New", Courier, monospace;
font-weight:bold;
font-family:Tahoma, Geneva, sans-serif;
}
#footer
{
text-align:center;
margin-top:50px;
}
that’s it we have created here Simple PHP Pagination with PDO using OOP concept.
Feel free to comment your suggestions regarding this tutorial.