How to Paginate PHP Dropdown Search Result with Datatable
How to Paginate PHP Dropdown Search Result with Datatable
gautambose
Posts: 8Questions: 3Answers: 0
I'm trying to get dropdown search results to paginate by the use of Datatable.But for some reason Datatable Pagination is not working properly. Is it possible to paginate search result differently? Anyone able to help please? Code is below: Index.php and loadData.php I am enclosing my search result
index.php
<?php
//index.php
$connect = new PDO("mysql:host=localhost;dbname=leadsqqe_all", "leadsqqe_bl", "gbose123BO*");
$query = "SELECT DISTINCT City FROM company ORDER BY City ASC";
$statement = $connect->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
<?php
>
?>
```html
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Leadstart test</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" rel="stylesheet" />
<link href="cssleadstart/bootstrap-select.min.css" rel="stylesheet" />
<script src="jsleadstart/bootstrap-select.min.js"></script>
<!-------------Data Table----->
<link href='https://cdn.datatables.net/1.10.21/css/jquery.dataTables.min.css' rel='stylesheet' type='text/css'>
<!-------------Data Table----->
<style>
#search_filter_city{
width: 50px;
height: 50px;
overflow-style: scrollbar
}
</style>
</head>
<body>
<div class="container">
<br />
<h2 align="center"></h2><br />
<select name="search_filter_city" id="search_filter_city" multiple class="form-control selectpicker">
<?php
foreach($result as $row)
{
echo '<option value="'.$row["City"].'">'.$row["City"].'</option>';
}
?>
</select>
<input type="hidden" name="hidden_city" id="hidden_city" />
<div style="clear:both"></div>
<br />
<div class="">
<table id="aero" class="display table table-striped table-bordered nowrap ui celled responsive page_table" style=" ">
<thead>
<tr>
<th>Company Name</th>
<th>City</th>
<th>State</th>
<th>Zip Code</th>
<th>Country</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
<br />
</div>
<!-------------Data Table----->
<script type="text/javascript" src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.min.js"></script>
```
```js
<script>
$(document).ready(function() {
$('#aero').DataTable({
"pagingType": "simple",
"searching": false,
"lengthChange": true,
"pageLength": 5
});
});
</script>
```js
<script>
$(document).ready(function(){
//load_data();
function load_data(query='')
{
$.ajax({
url:"loadData.php",
method:"POST",
data:{query:query},
success:function(data)
{
$('tbody').html(data);
}
})
}
$('#search_filter_city').change(function(){
$('#hidden_city').val($('#search_filter_city').val());
var query = $('#hidden_city').val();
load_data(query);
});
});
</script>
</body>
</html>
loaddata.php
```php
<?php
//fetch.php
$connect = new PDO("mysql:host=localhost;dbname=leadsqqe_all", "leadsqqe_bl", "gbose123BO*");
if($_POST["query"] != '')
{
$search_array = explode(",", $_POST["query"]);
$search_text = "'" . implode("', '", $search_array) . "'";
$query = "
SELECT * FROM company
WHERE City IN (".$search_text.")
ORDER BY companyID DESC
";
}
else
{
$query = "SELECT * FROM company ORDER BY companyID DESC";
}
$statement = $connect->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
$total_row = $statement->rowCount();
$output = '';
if($total_row > 0)
{
foreach($result as $row)
{
$output .= '
<tr>
<td>'.$row["CompanyName"].'</td>
<td>'.$row["City"].'</td>
<td>'.$row["State"].'</td>
<td>'.$row["ZipCode"].'</td>
<td>'.$row["Country"].'</td>
</tr>
';
}
}
else
{
$output .= '
<tr>
<td colspan="5" align="center">No Data Found</td>
</tr>
';
}
echo $output;
<?php > ```  ?>
This question has accepted answers - jump to:
Answers
Looks like you are initializing Datatables before the data is loaded into the table. So Datatables sees an empty table. You are adding the table data directly to the DOM which Datatables doesn't know about so it always reports the table as empty.
Try moving the Datatables init code into the
successfunction of your Ajax call. Place it after the$('tbody').html(data);statement, like this:Kevin
Thank You Very Much
Following message I receive
DataTables warning: table id=aero not reinitialise DataTable. For more information about this error, please see http://datatables.net/tn/3
Did you read the diagnosis provided by the link? http://datatables.net/tn/3
Yes
I'm guessing by the one word response that the link didn't help you solve the problem. If you are calling
load_data()more than once then you will cause a reinitialization of the Datatable with the change I suggested. In order to reinitialize the Datatable you can add thedestroyoption as shown in this section of the technote. Like this:Kevin
Thank You.It's OK Now