I created bootstrap table for my word-press site.I created custom template to publish my table on my website.The problem is that my tables has a 2000 pages and each pages show 10 entries and consists of 7 columns.It took so much time to load this table.How can i avoid from this problem.Please help me on this and i am open to any other ways.Thank you for your kind support.
index.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Demo Server Side Process Datatable</title>
<!-- bootstrap Lib -->
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href=".3.1/css/bootstrap.min.css">
<script src=".4.1/jquery.min.js"></script>
<script src=".js/1.14.7/umd/popper.min.js"></script>
<script src=".3.1/js/bootstrap.min.js"></script>
<!-- datatable lib -->
<link rel="stylesheet" href=".10.19/css/jquery.dataTables.min.css">
<script src=".3.1.js"></script>
<script src=".10.19/js/jquery.dataTables.min.js"></script>
</head>
<body>
<div class="container">
<h1>Server Side Process Datatable</h1>
<table id="example" class="display" cellspacing="0" width="100%">
<thead>
<tr>
<th>ID</th>
<th>Title</th>
<th>Year</th>
<th>Categories</th>
<th>Tags</th>
<th>Type</th>
<th>Download Link</th>
</tr>
</thead>
<tfoot>
<tr>
<th>ID</th>
<th>Title</th>
<th>Year</th>
<th>Categories</th>
<th>Tags</th>
<th>Type</th>
<th>Download Link</th>
</tr>
</tfoot>
</table>
<!--create modal dialog for display detail info for edit on button cell click-->
<div class="modal fade" id="myModal" role="dialog">
<div class="modal-dialog">
<div id="content-data"></div>
</div>
</div>
</div>
<script>
$(document).ready(function(){
var dataTable=$('#example').DataTable({
"processing": true,
"serverSide":true,
"ajax":{
url:"fetch.php",
type:"post"
}
});
});
</script>
i share my code above.At the the end it will have a 20000 rows.Therefore i want to create a true pagination for this table.
fetch.php
<?php
$con = @mysqli_connect('localhost', 'root', '', 'blabla');
if (!$con) {
echo "Error: " . mysqli_connect_error();
exit();
}
echo 'Connected to MySQL';
$request=$_REQUEST;
$col =array(
0 => 'ID',
1 => 'Title',
2 => 'Year',
3 => 'Categories',
4 => 'Tags',
5 => 'Type',
6 => 'Download Link',
); //create column like table in database
$sql ="SELECT * FROM Table_46";
$totalData=mysqli_num_rows($query);
$totalFilter=$totalData;
//Search
$sql ="SELECT * FROM Table_46 WHERE 1=1";
if(!empty($request['search']['value'])){
$sql.=" AND (id Like '".$request['search']['value']."%' ";
$sql.=" OR Title Like '".$request['search']['value']."%' ";
$sql.=" OR Year Like '".$request['search']['value']."%' ";
$sql.=" OR Categories Like '".$request['search']['value']."%' )";
}
$query=mysqli_query($con,$sql);
$totalData=mysqli_num_rows($query);
//Order
$sql.=" ORDER BY ".$col[$request['order'][0]['column']]." ".$request['order'][0]['dir']." LIMIT ".
$request['start']." ,".$request['length']." ";
$query=mysqli_query($con,$sql);
$data=array();
while($row=mysqli_fetch_array($query)){
$subdata=array();
$subdata[]=$row[0]; //id
$subdata[]=$row[1]; //name
$subdata[]=$row[2]; //salary
$subdata[]=$row[4]; //age
$subdata[]=$row[5];
$subdata[]=$row[6];
//create event on click in button edit in cell datatable for display modal dialog $row[0] is id in table on database
$subdata[]='<button type="button" id="getEdit" class="btn btn-primary btn-xs" data-toggle="modal" data-target="#myModal" data-id="'.$row[0].'"><i class="glyphicon glyphicon-pencil"> </i>Edit</button>
<button type="button" class="btn btn-danger btn-xs"><i class="glyphicon glyphicon-trash"> </i>Delete</button>';
$data[]=$subdata;
}
$json_data=array(
"draw" => intval($request['draw']),
"recordsTotal" => intval($totalData),
"recordsFiltered" => intval($totalFilter),
"data" => $data
);
echo json_encode($json_data);
?>