In this post you can find the solution of How to navigate to a specific page in jQuery DataTable with Ajax PHP. In jQuery DataTable there is pagination feature is available for go next page or previous page or first page or last page or any five number page link is available for view page data in jquery datatable but suppose there is large table with more than 1000 pages, and that data has been load in jQuery DatTable and there are multiple pagination link will be made by jQuery DataTable plugin, now we want to go to any specific page number data directly or jump to the specific page number. So at that time we want to required Select box pagination is required with DataTable default pagination. So in select box we can find list of page number. So when we want to go to specific page then we have to simply select page number, and jQuery DataTable will load that data with server-side processing using Ajax PHP.
jQuery DataTable has pagination plugin like Navigation with text input field pagination and Select list pagination plugin. So when we have use this jQuery DataTable plugin, then DataTable default pagination link has been removed and that Input textbox or Select box pagination will be display. But we want to add select box pagination with jQuery DataTable default pagination. For implement this feature, we have lots of search on internet regarding how can we have implement select box pagination with jQuery Datatable default pagination. After spend lots of time, we have to decide to make custom select box pagination in jQuery DataTable plugin. But in jquery DataTable there is not so easy to add custom select box pagination with DataTable Table pagination.
After do many research, we have found jQuery page api info() method. This page.info() api has return details of jquery DataTable page information. By this method we can get the value of current page number, length of data etc. By using this information, we have make custom select box with data. So when DataTable data has been change then it will make fill page number in select box using drawCallback function. This function will be called on every refresh of data. So After filling select box with page number data. Now we want to write simple jQuery like when we have select page number from select box, then it will execute that block of code. Under this code, it will first fetch start and length variable value from selected page option data attribute and based on this data it will load that page data in jQuery DataTable.
By this code you can implement two different type of pagination on jQuery DataTable at the same time. One is the default pagination of DataTable and other one is this custom select box pagination. So if you want to add custom select box pagination in jQuery DataTable then below source code will help you and you can add feature like navigate page to specific number of page in jQuery DataTable. This feature will help you when you have load large data in jQuery DataTable and you want to view any specific page number data. I hope you have understand this post and you have got success in implement two different type of pagination in jQuery DataTable with Ajax jQuery. Below you can find source code.
Source Code
index.php
<html>
<head>
<title>jQuery DataTable Jump to a Specific Page with PHP Ajax</title>
<!-- JS, Popper.js, and jQuery -->
<script src="https://code.jquery.com/jquery-3.5.1.js" integrity="sha256-QWo7LDvxbWT2tbbQ97B53yJnYU3WhH/C8ycbRAkjPDc=" crossorigin="anonymous"></script>
<!-- CSS only -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" integrity="sha384-JcKb8q3iqJ61gNV9KGb8thSsNjpSL0n8PARn9HuZOnIxN0hoP+VmmDGMN5t9UJ0Z" crossorigin="anonymous">
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.1/dist/umd/popper.min.js" integrity="sha384-9/reFTGAW83EW2RDu2S0VKaIzap3H66lZH81PoYlFhbGU+6BZp6G7niu735Sk7lN" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js" integrity="sha384-B4gt1jrGC7Jh4AgTPSdUtOBvfO8shuf57BaghqFfPlYxofvL8/KUEfYiJOMMV+rV" crossorigin="anonymous"></script>
<script src="https://cdn.datatables.net/1.10.22/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.22/js/dataTables.bootstrap4.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.22/css/dataTables.bootstrap4.min.css" />
</head>
<body>
<div class="container">
<br />
<h1 align="center" class="text-primary"><b>jQuery DataTable Jump to a Specific Page with PHP Ajax</b></h1>
<br />
<div class="card">
<div class="card-header">
<div class="row">
<div class="col-md-9">Customer Data</div>
<div class="col-md-3">
<div class="input-group">
<div class="input-group-prepend">
<span class="input-group-text">Page</span>
</div>
<select name="pagelist" id="pagelist" class="form-control"></select>
<div class="input-group-append">
<span class="input-group-text">of <span id="totalpages"></span></span>
</div>
</div>
</div>
</div>
</div>
<div class="card-body">
<div class="table-responsive">
<table id="customer_table" class="table table-bordered table-striped">
<thead>
<tr>
<th>Customer ID</th>
<th>Customer First Name</th>
<th>Customer Last Name</th>
<th>Customer Email</th>
<th>Customer Gender</th>
</tr>
</thead>
</table>
</div>
</div>
</div>
</div>
<br />
<br />
</body>
</html>
<script type="text/javascript" language="javascript">
$(document).ready(function(){
function load_data(start, length)
{
var dataTable = $('#customer_table').DataTable({
"processing" : true,
"serverSide" : true,
"order" : [],
"retrieve": true,
"ajax" : {
url:"fetch.php",
method:"POST",
data:{start:start, length:length}
},
"drawCallback" : function(settings){
var page_info = dataTable.page.info();
$('#totalpages').text(page_info.pages);
var html = '';
var start = 0;
var length = page_info.length;
for(var count = 1; count <= page_info.pages; count++)
{
var page_number = count - 1;
html += '<option value="'+page_number+'" data-start="'+start+'" data-length="'+length+'">'+count+'</option>';
start = start + page_info.length;
}
$('#pagelist').html(html);
$('#pagelist').val(page_info.page);
}
});
}
load_data();
$('#pagelist').change(function(){
var start = $('#pagelist').find(':selected').data('start');
var length = $('#pagelist').find(':selected').data('length');
load_data(start, length);
var page_number = parseInt($('#pagelist').val());
var test_table = $('#customer_table').dataTable();
test_table.fnPageChange(page_number);
});
});
</script>
fetch.php
<?php
//fetch.php
$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");
$column = array("customer_id", "customer_first_name", "customer_last_name", "customer_email", "customer_gender");
$query = "SELECT * FROM customer_table ";
if(isset($_POST["search"]["value"]))
{
$query .= '
WHERE customer_id LIKE "%'.$_POST["search"]["value"].'%"
OR customer_first_name LIKE "%'.$_POST["search"]["value"].'%"
OR customer_last_name LIKE "%'.$_POST["search"]["value"].'%"
OR customer_email LIKE "%'.$_POST["search"]["value"].'%"
OR customer_gender LIKE "%'.$_POST["search"]["value"].'%"
';
}
if(isset($_POST['order']))
{
$query .= 'ORDER BY '.$column[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' ';
}
else
{
$query .= 'ORDER BY customer_id DESC ';
}
$query1 = '';
if($_POST['length'] != -1)
{
$query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$statement = $connect->prepare($query);
$statement->execute();
$number_filter_row = $statement->rowCount();
$result = $connect->query($query . $query1);
$data = array();
foreach($result as $row)
{
$sub_array = array();
$sub_array[] = $row['customer_id'];
$sub_array[] = $row['customer_first_name'];
$sub_array[] = $row['customer_last_name'];
$sub_array[] = $row['customer_email'];
$sub_array[] = $row['customer_gender'];
$data[] = $sub_array;
}
function count_all_data($connect)
{
$query = "SELECT * FROM customer_table";
$statement = $connect->prepare($query);
$statement->execute();
return $statement->rowCount();
}
$output = array(
"draw" => intval($_POST["draw"]),
"recordsTotal" => count_all_data($connect),
"recordsFiltered" => $number_filter_row,
"data" => $data
);
echo json_encode($output);
?>
I have not been able to get this to work. I get the error message:"DataTables warning: table id=customer_table - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1" The fetch appears to time out. Is there a diiference in the demo that is working?
ReplyDeleteme too ,any solution?
Deleteinspireypting@gmail.com
ReplyDeletesoliution: ned PDO Mysql driver view echo phpinfo()
ReplyDelete