I'm trying to sort some columns on my Datatable using php code and ajax but it's not working properly. In my research i found some questions about this theme but unfortunately i didn't get the correct way to improve my php code for this. Below are the questions that i mentioned here:
I have one table that call users where it contains the following structure:
id | first_name | last_name
______________________________
01 | Joseph | Jackson
02 | Carlos | Foster
03 | Natasha | Fuller
04 | John | Brandon
And i have one index.php page that display DataTable with the data above:
<body>
<div class="container box">
<h1 align="center">PHP PDO Ajax CRUD with Data Tables </h1>
<br />
<div class="table-responsive">
<br />
<table id="user_data" class="table table-bordered table-striped">
<thead>
<tr>
<th width="35%">First Name</th>
<th width="35%">Last Name</th>
<th width="10%">Edit</th>
<th width="10%">Delete</th>
</tr>
</thead>
</table>
</div>
</div>
</body>
In this index.php page, it has an Ajax code that call a php script that fetch data to display on Datatable:
<script type="text/javascript" language="javascript" >
$(document).ready(function(){
var dataTable = $('#user_data').DataTable({
"processing":true,
"serverSide":true,
"order":[],
"ajax":{
url:"fetch.php",
type:"POST"
},
"columnDefs":[
{
"targets":[2,3],
"orderable":false,
},
],
});
});
Below is the php script (fetch.php) that contain the code to fetch data and send to Ajax:
<?php
include('db.php');
$query = '';
$output = array();
$query .= "SELECT * FROM users ";
if(isset($_POST["search"]["value"]))
{
$query .= 'WHERE first_name LIKE "%'.$_POST["search"]["value"].'%" ';
$query .= 'OR last_name LIKE "%'.$_POST["search"]["value"].'%" ';
}
if(isset($_POST["order"]))
{
$query .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
}else
{
$query .= 'ORDER BY id DESC ';
}
if($_POST["length"] != -1)
{
$query .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$statement = $connection->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
$data = array();
$filtered_rows = $statement->rowCount();
foreach($result as $row)
{
$sub_array = array();
$sub_array[] = $row["first_name"];
$sub_array[] = $row["last_name"];
$sub_array[] = '<button type="button" name="update" id="'.$row["id"].'" class="btn btn-warning btn-xs update">Update</button>';
$sub_array[] = '<button type="button" name="delete" id="'.$row["id"].'" class="btn btn-danger btn-xs delete">Delete</button>';
$data[] = $sub_array;
}
$output = array(
"draw" => intval($_POST["draw"]),
"recordsTotal" => $filtered_rows,
"recordsFiltered" => get_total_all_records(),
"data" => $data
);
echo json_encode($output);
?>
As you can see above, Datatable display data and works fine but when i try to sort first_name column for example, the table not sorting correctly like A-Z or Z-A for example. In this link below, is based on my doubt and if you try to sort column you will observe that the sorting are not correct:
http://demo.webslesson.info/php-pdo-ajax-crud-datatables/
In this case, what can i do to improve my php or Ajax code to Datatable sorting correctly like A-Z/Z-A?
