search data from mysql database and display it in Datatable
search data from mysql database and display it in Datatable
khushal21
Posts: 2Questions: 1Answers: 0
I am using datatable to display the data but as the database is too large it takes long time to load the data. So to reduce the time I have set a limit of 200 rows in php query. Now the search filters are the primary need for me in datatable.
So to search data I have a jquery function that is called on keyup event and makes a ajax call to another file which contains the php select query that search the string that is posted by ajax. Now I want to display the result of the query on the same datatable by altering data of the datatable by these resultant data rows.
It might be more clear by seeing the code.
Here is the first file which displays 200 rows from the database
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<!-- The above 3 meta tags *must* come first in the head; any other head content must come *after* these tags -->
<title>Trial 2</title>
<!-- Bootstrap -->
<link href="css/bootstrap.min.css" rel="stylesheet">
<link href="datatable/css/jquery.dataTables.min.css" rel="stylesheet">
<!--<link href="https://cdn.datatables.net/1.10.15/css/dataTables.bootstrap.min.css" rel="stylesheet">-->
<style>
tfoot input {
width: 100%;
padding: 3px;
box-sizing: border-box;
}
</style>
</head>
<body>
<br><br>
<form action="" method="POST">
<div class="container">
<div class="row">
<table id="example" class="table table-striped table-bordered" cellspacing="0" width="100%">
<thead>
<tr>
<th>Name</th>
<th>Surname</th>
<th>City</th>
<th>Mobile No</th>
</tr>
</thead>
<tfoot>
<tr>
<th><input type="text" name="name" id="name" placeholder="Search by NAME"></th>
<th>Surname</th>
<th>City</th>
<th>Mobile No</th>
</tr>
</tfoot>
<tbody>
<?php
require('connectivity2.php');
$query1 = "SELECT * FROM member LIMIT 200;";
$result1 = mysqli_query($con, $query1);
while($row = mysqli_fetch_assoc($result1)){
$name = $row['m_name'];
$surname = $row['m_sec_surname'];
$city = $row['m_city'];
$no = $row['m_mobile'];
?>
<tr>
<td><?php echo $name; ?></td>
<td><?php echo $surname; ?></td>
<td><?php echo $city; ?></td>
<td><?php echo $no; ?></td>
</tr>
<?php } //End of while loop
?>
</tbody>
</table>
</div>
</div>
</form>
<script src="js/jquery-1.12.4.js"></script>
<script src="datatable/js/jquery.dataTables.min.js"></script>
<!--<script src="https://cdn.datatables.net/1.10.15/js/dataTables.bootstrap.min.js"></script> -->
<script type="text/javascript">
$(document).ready(function() {
// DataTable
var table = $('#example').DataTable();
// Search
$("#name").keyup(function(){
var m_name = $("input[name='name']").val();
//window.alert(m_name);
$.ajax({
type: "POST",
url: "trial2php.php",
data: {member_name : m_name},
cache: true,
success: function(){
$('#example').DataTable)();
}
});
});
} );
</script>
</body>
</html>
And the second file code is below here ..
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<!-- The above 3 meta tags *must* come first in the head; any other head content must come *after* these tags -->
<title>Trail 2</title>
<!-- Bootstrap -->
<link href="css/bootstrap.min.css" rel="stylesheet">
<link href="datatable/css/jquery.dataTables.min.css" rel="stylesheet">
<!--<link href="https://cdn.datatables.net/1.10.15/css/dataTables.bootstrap.min.css" rel="stylesheet">-->
<style>
tfoot input {
width: 100%;
padding: 3px;
box-sizing: border-box;
}
</style>
</head>
<body>
<br><br>
<div class="container">
<div class="row">
<table id="example" class="table table-striped table-bordered" cellspacing="0" width="100%">
<thead>
<tr>
<th>Name</th>
<th>Surname</th>
<th>City</th>
<th>Mobile No</th>
</tr>
</thead>
<tfoot>
<tr>
<th><input type="text" name="name" id="name" placeholder="Search by NAME"></th>
<th>Surname</th>
<th>City</th>
<th>Mobile No</th>
</tr>
</tfoot>
<tbody>
<?php
require('connectivity2.php');
if(isset($POST['member_name']){
$member_name = $POST['member_name'];
$query1 = "SELECT * FROM member WHERE m_name LIKE '%$member_name%';";
$result1 = mysqli_query($con, $query1);
while($row = mysqli_fetch_assoc($result1)){
$name = $row['m_name'];
$surname = $row['m_sec_surname'];
$city = $row['m_city'];
$no = $row['m_mobile'];
?>
<tr>
<td><?php echo $name; ?></td>
<td><?php echo $surname; ?></td>
<td><?php echo $city; ?></td>
<td><?php echo $no; ?></td>
</tr>
<?php }//End of while loop
}//End of if statement
?>
</tbody>
</table>
</div>
</div>
<script src="js/jquery-1.12.4.js"></script>
<script src="datatable/js/jquery.dataTables.min.js"></script>
<!--<script src="https://cdn.datatables.net/1.10.15/js/dataTables.bootstrap.min.js"></script> -->
<script type="text/javascript">
$(document).ready(function() {
// DataTable
var table = $('#example').DataTable();
// Search
$("#name").keyup(function(){
var m_name = $("input[name='name']").val();
$.ajax({
type: "POST",
url: "trial2php.php",
data: {member_name : m_name},
cache: true,
success: function(){
$('#example').DataTable();
}
});
}
} );
</script>
</body>
</html>
So what I want is to display the data in the same datatable on keyup event.
This discussion has been closed.
Answers
Why don't you use server-side processing?
https://datatables.net/examples/data_sources/server_side.html
How could i use that ?
There is no server side script code there and I am not clear with that code.
The page I showed has a link to various examples with complete code.
See also the FAQ; Q. DataTables is running slow. How can I speed it up?