So let’s start the coding. We will have following file structure for data scraping tutorial
- index.php
- db_connect.php
- pagination_data.php
- paginate.js
Steps1: For pagination example, we will load data from MySQL database, so we need to create Database Table to store and get data.
CREATE TABLE `employee` (
`id` int(11) NOT NULL COMMENT 'primary key',
`employee_name` varchar(255) NOT NULL COMMENT 'employee name',
`employee_salary` double NOT NULL COMMENT 'employee salary',
`employee_age` int(11) NOT NULL COMMENT 'employee age'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Steps2: After creating MySQL database table, we will create db_connect.php file to make connection with MySQL database.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "phpzag_demos";
$conn = mysqli_connect($servername, $username, $password, $dbname);
?>
Steps3: Now we will include jquery datatable and jquery library files.
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.12/css/jquery.dataTables.css">
<script type="text/javascript" charset="utf8" src="//cdn.datatables.net/1.10.12/js/jquery.dataTables.js"></script>
Steps4: In index.php, we will create datatable HTML to display data in it.
<div class="container">
<h2>jQuery DataTable Pagination using PHP & MySQL</h2>
<div class="row">
<table id="example" class="display" width="100%" cellspacing="0">
<thead>
<tr>
<th>Empid</th>
<th>Name</th>
<th>Salary</th>
</tr>
</thead>
</table>
</div>
<div style="margin:50px 0px 0px 0px;">
<a class="btn btn-default read-more" style="background:#3399ff;color:white" href="https://www.phpzag.com/datatable-pagination-using-php-mysql" title="">Back to Tutorial</a>
</div>
</div>
Steps5: Now in paginate.js, we will create data table using jQuery datatable plugin and make an ajax request to server side pagination_data.php to get data from MySQL database table to display in Datatable. We will use bPaginate:true to create pagination, iDisplayLength: 5 to display 5 records at a time and sPaginationType:"full_numbers" to display full pagination options.
jQuery( document ).ready(function() {
var table = jQuery('#example').dataTable({
"bProcessing": true,
"sAjaxSource": "pagination_data.php",
"bPaginate":true,
"sPaginationType":"full_numbers",
"iDisplayLength": 5,
"bLengthChange":false,
"bFilter": false,
"aoColumns": [
{ mData: 'Empid' } ,
{ mData: 'Name' },
{ mData: 'Salary' }
]
});
});
Steps6: Finally in pagination_data.php, we will get data from MySQL database and returned as JSON through php function json_encode with datatable plugin pagination options. We will return iTotalRecords and iTotalDisplayRecords values to for jQuery Datatable pagination.
<?php
include_once("db_connect.php");
$sql = "SELECT id as Empid,employee_name as Name,employee_salary as Salary FROM employee LIMIT 20";
$resultset = mysqli_query($conn, $sql) or die("database error:". mysqli_error($conn));
$data = array();
while( $rows = mysqli_fetch_assoc($resultset) ) {
$data[] = $rows;
}
$results = array(
"sEcho" => 1,
"iTotalRecords" => count($data),
"iTotalDisplayRecords" => count($data),
"aaData"=>$data);
echo json_encode($results);
exit;
?>
You can view the live demo from the Demo link and can download the script from the Download link below.
Demo [sociallocker]Download[/sociallocker]