<li class="has_sub">
<a href="/users/dynamicDatatable" class="waves-effect waves-primary">
<i class="ti-menu-alt"></i><span> Dynamic DataTable </span>

</a>

</li>




<%- include('header') %>
<!-- ============================================================== -->
<!-- Start right Content here -->
<!-- ============================================================== -->
<div class="content-page">
<!-- Start content -->
<div class="content">
<div class="container-fluid">

<!-- Page-Title -->
<div class="row">
<div class="col-sm-12">
<div class="page-title-box">
<h4 class="page-title">Application Form</h4>
<ol class="breadcrumb float-right">

<li class="breadcrumb-item"><a href="#">Pages</a></li>
<li class="breadcrumb-item active">Dashboard</li>
</ol>
<div class="clearfix"></div>
</div>
</div>
</div>

<div class="row">
<div class="col-12">
<div class="card-box">
<h4 class="m-t-0 header-title"> Dynamic DataTable Using Ajax</h4>




<div class="row">
<div class="col-12">
<div class="p-20">
<table class="table tabs-bordered" id="customer_data">
<thead>

<th>Name</th>
<th>Address</th>
<th>email</th>
<th>Mobile</th>

</thead>
<tbody>

</tbody>
</table>





</div>
</div>

</div>
<!-- end row -->

</div> <!-- end card-box -->
</div><!-- end col -->
</div>
<!-- end row -->


</div>
<!-- end container -->
</div>
<!-- end content -->



<%- include('footer') %>

<script>
$(document).ready( function () {
var datatable = $('#customer_data').DataTable({
'processing':true,
'serverSide': true,
'serverMethod':'get',
'ajax':{
'url':'/users/getdynamicdata/',
},
'aaSorting':[],
'columns':[
{data:'name'},
{data:'address'},
{data:'email'},
{data:'mobile'}
],

});
});
</script>

router.get('/dynamicDatatable',function(req,res,next){
res.render('dynamic-datatable',{title:'Dynamic DataTable'});
});
router.get('/getdynamicdata',function(req,res,next){
var draw = req.query.draw;
var start = req.query.start;
var length = req.query.length;
var order_data = req.query.order;

if(typeof order_data === 'undefined')
{
var column_name = 'user_details.id';
var column_sort_order = 'desc';
}
else{
var column_index = req.query.order[0]['column'];
var column_name = req.query.columns[column_index]['data'];
var column_sort_order = req.query.order[0]['dir'];

}

// search data
var search_value = req.query.search['value'];
// var search_query = " AND (name LIKE '%${search_value}%' OR Address LIKE '%${search_value}%' OR email LIKE '%${search_value}%' OR mobile LIKE '%${search_value}%')";
var search_query = " (name LIKE '%"+search_value+"%' OR Address LIKE '%"+search_value+"%' OR email LIKE '%"+search_value+"%' OR mobile LIKE '%"+search_value+"%')";
conn.query("select count(*) as total from user_details", function(error,data){
var total_records = data[0].total;
// conn.query("select count(*) as total from user_details where 1 ${search_query}",function(error,data){
var sql = "select count(*) as total from user_details where 1 AND "+search_query;
conn.query(sql,function(error,data){
var total_records_with_filter = data[0].total;
// var query = "select * from user_details where 1 ${search_query} order by ${column_name} ${column_sort_order} LIMIT ${start}, ${lenght}";
var query = "select * from user_details where 1 AND "+search_query+ "order by '"+column_name+"' '"+column_sort_order+"' LIMIT "+start+ ',' +length;
var data_arr = [];
conn.query(query,function(error,data){
data.forEach(function(row){
data_arr.push({
'name' : row.name,
'address' : row.Address,
'email' : row.email,
'mobile' : row.mobile
});
});

var output = {
'draw' : draw,
'iTotalRecords' : total_records,
'iTotalDisplayRecords' : total_records_with_filter,
'aaData' : data_arr
}
res.json(output);
});

});
});


})

CREATE TABLE `user_details` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(64) NOT NULL COLLATE 'utf8mb4_0900_ai_ci',
`email` VARCHAR(64) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
`mobile` BIGINT(19) NULL DEFAULT NULL,
`Address` TEXT NOT NULL COLLATE 'utf8mb4_0900_ai_ci',
PRIMARY KEY (`id`)
);