Please how can I edit this datatable to show Page Row Selector
Please how can I edit this datatable to show Page Row Selector
mnwuzor
Posts: 4Questions: 2Answers: 0
Good afternoon.
Please I need your kind assistance. I have been working on a PHP MySQL web project and everything is going well. However, I have a datatable that seem to be malfunctioning.
The code below is that of a datatable gotten from this site https://www.webslesson.info/2016/10/datatables-jquery-plugin-with-php-mysql-and-bootstrap.html.
The datatable runs well and shows the search box, page navigation and page row selector (10, 25, 50, 100) correctly for tables with less than a 100 records but each time I connect it to a table that has more than 200,000 records, the table malfunctions. That is it hides the search box, page navigation and page row selector and then loads records endlessly.
My question now is how I can make the page row selector (10, 25, 50, 100) to work properly such that irrespective of the number of records in the table, the page will load the default 10 records, show the search box and the page navigation buttons at the bottom of the table.
Thank you
<?php
$connect = mysqli_connect("localhost", "root", "", "crirs");
$query ="SELECT * FROM tbl_tinregister ORDER BY fullname ASC";
$result = mysqli_query($connect, $query);
?>
(<!DOCTYPE html>
<html>
<head>
<title>Webslesson Tutorial | Datatables Jquery Plugin with Php MySql and Bootstrap</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
<script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
</script>
</head>
<body>
<br /><br />
<div class="container">
<h3 align="center">Datatables Jquery Plugin with Php MySql and Bootstrap</h3>
<br />
<div class="table-responsive">
<table id="stock" class="table table-striped table-bordered" >
<thead>
<tr>
<td>ID</td>
<td>Name</td>
<td>TIN</td>
<td>Type</td>
<td data-sortable="false">Action</td>
<td data-sortable="false">Action</td>
<td data-sortable="false">Action</td>
</tr>
</thead>
<tbody>
<?php
while($row = mysqli_fetch_array($result))
{
echo '
<tr>
<td>'.$row["tin_id"].'</td>
<td>'.$row["fullname"].'</td>
<td>'.$row["taxidno"].'</td>
<td>'.$row["type"].'</td>
<td><a href = "add.php?id='.$row["tin_id"].'">Stock In</a></td>
<td><a href = "modify.php?id='.$row["tin_id"].'">Modify</a></td>
<td><a href = "delete.php?id='.$row["tin_id"].'">Delete</a></td>
</tr>
';
}
?>
</tbody>
</table>
</div>
</div>
//Javascript part for datatable
<script>
$(document).ready(function() {
$('#stock').DataTable({
"data": data,
"deferRender": true,
"scrollY": 200,
"scrollCollapse": true,
"scroller": {
loadingIndicator: true
}
"ajax": "data/arrays.txt",
"processing": true,
"serverSide": true,
"ajax": "server_side/scripts/server_processing.php"
} );
}
);
</script>
</body>
</html>
Answers
Hi @mnwuzor ,
With
serverSideenabled, it should always just return the data required for that page. However, you've got a couple of odd things going on. First, you're usingdata, which means you're loading local data, this contradictsserverSide. And secondly, and most important,ajaxshould be pointing to a script that returns the correct data for that page - yours is just a text file, so it's incapable of complying with the client's requests.It would be worth looking at this page here which discusses what you need.
Cheers,
Colin
Hi Colin,
Thank you so much for the response.
After pulling the data using server side, how easy can I add edit and delete buttons on the table? This has been my challenge. I am a novice in Ajax and JSon.
Thank you
Hi @mnwuzor ,
It's very easy if you're using Editor, this extension provides that. This simple example shows how easy it is.
Cheers,
Colin
@colin Thank you so much sir. Its quite helpful