Datatables won't sort ajax from join query
Datatables won't sort ajax from join query
Stacey1134
Posts: 112Questions: 20Answers: 0
Link to test case: I cannot find a test case
Debugger code (debug.datatables.net): doesn't work as loaded datatables are inside tabcontents inside bootstrap 5 cards from an external file.
Error messages shown: No error messages, It just won't sort.
Description of problem:
Okay, I looked and looked to an answer, but have found none so here goes. Also,
I am relatively new to datatables. On all my other datatable where I am using ajax calls, I can sort quite fine, especially ascending and descending order. However, I have a table generated by a join select query which will not sort. I have tried all the instructions in forums, and the manual. Those options do work, (I can see the changes in sorting bar) but the data won't sort. Lastly when I try to order it without using datatable variables, it won't show any data in the table at all.
To be clear, I want the user to just click the standard arrows on the datatable and sort by record date.
(One other weird thing that might be relevant. On this particular datatable, it shows no records until I put data in the search box. Although I like it better that way with the amount of records, I still can't figure out how it is happening. It does show the total number of records filtered though).
historytable.php
<script>
$(document).ready(function () {
var table = $('#historyTable').DataTable({
'processing': true,
'serverSide': true,
'responsive': true,
'lengthChange': true,
'serverMethod': 'post',
'ajax': {
'url':'ajaxHistoryTable.php'
},
dom: 'Bfrtip',
buttons: [
{
text: '<i class="fa-sharp fa-solid fa-user-plus"></i>Add New',
className: 'btn btn-success btn-sm btn-rounded',
action: function ( e, dt, node, config ) {
$('#editHistoryModal').attr('data-neworedit', 'CREATE');
loadHistoryModal();
}
}
],
// 'columnDefs': [
// { orderable: false, targets: 0 }
// ],
// 'order': [[0, 'desc']],
'columns':[
// Shown Columns
{data:'Rec_Date'},
{data:'firstName'},
{data:'lastName'},
{data:'prisonerid'},
{data:'ContactType'},
{data:'entryBy'},
// UI Buttons
{data: null, 'defaultContent': '<button type="button" class="btn btn-warning" id="btnEdit"><i class="fa-solid fa-pencil" ></i></button> <button type="button" class="btn btn-danger" id="btnDelete"><i class="fa-solid fa-trash-can" ></i></button>'},
// Hidden Columns, appended to the end of the table
{data:'CR_ID', visible: false},
{data:'SF_ID', visible: false},
{data:'Comments', visible: false, searchable: false},
]
});
$('#historyTable tbody').on('click', 'button', function () {
var action = this.id;
var data = table.row($(this).parents('tr')).data();
if(action=='btnEdit') {
// Attach row data to the modals data attributes
$('#editHistoryModal')
.attr('data-neworedit', 'UPDATE') // this attribute allows for both the modal and update php file to be used for both adding and editing
.attr('data-recdate', data['Rec_Date'])
.attr('data-crid', data['CR_ID'])
.attr('data-sfid', data['SF_ID'])
.attr('data-contacttype', data['ContactType'])
.attr('data-comments', data['Comments'])
.attr('data-fname', data['firstName'])
.attr('data-lname', data['lastName'])
.attr('data-pid', data['prisonerid'])
.attr('data-lasteditedby', data['entryBy']);
// Function that call modal that allows for editing because modal cannot be called from this click event.
loadUserModal();
};
if(action=='btnDelete') {
// Attach row data to the modals data attributes
alert("Would have deleted Record!");
}
});
});
function loadHistoryModal() {
$('#editHistoryModal').modal('show');
}
</script>
<table id="historyTable" class="display" style="width:100%">
<thead>
<tr>
<th>Log Date</th>
<th>First Name</th>
<th>Last Name</th>
<th>Prisoner ID</th>
<th>Contact Type</th>
<th>Entered By</th>
<th>Action</th>
<th>Contact ID</th>
<th>Salesforce ID</th>
<th>Comments</th>
</tr>
</thead>
<tfoot>
<tr>
<th>Log Date</th>
<th>First Name</th>
<th>Last Name</th>
<th>Prisoner ID</th>
<th>Contact Type</th>
<th>Entered By</th>
<th>Action</th>
<th>Contact ID</th>
<th>Salesforce ID</th>
<th>Comments</th>
</tr>
</tfoot>
</table>
ajaxhistorytable.php
```
<?php
//Database connection by using PHP PDO
require_once "config.php";
$dbh = new PDO("mysql:host=".$dbserver.";dbname=".$dbdatabase.";charset=utf8", $dbusername, $dbpassword); // Create Object of PDO class by connecting to Mysql database
Read value
$draw = $_POST['draw'];
$row = $_POST['start'];
$rowperpage = $_POST['length']; // Rows display per page
$columnIndex = $_POST['order'][0]['column']; // Column index
$columnName = $_POST['columns'][$columnIndex]['data']; // Column name
$columnSortOrder = $_POST['order'][0]['dir']; // asc or desc
$searchValue = $_POST['search']['value']; // Search value
$searchArray = array();
Search
$searchQuery = " ";
if($searchValue !== ''){
$searchArray = array('srchValue'=>"%$searchValue%");
}
Total number of records without filtering
$stmt = $dbh->prepare("SELECT COUNT(*) AS allcount
FROM form
LEFT JOIN HistoryLog
ON form.CR_ID = HistoryLog.CR_ID ");
$stmt->execute();
$records = $stmt->fetch();
$totalRecords = $records['allcount'];
Total number of records with filtering
$stmt = $dbh->prepare("SELECT COUNT(*) AS allcount
FROM form
LEFT JOIN HistoryLog
ON form.CR_ID = HistoryLog.CR_ID
WHERE CONCAT(form.FirstName, ' ', form.LastName)
LIKE :srchValue
OR CONCAT_WS(form.CR_ID, form.PrisonerID,form.FirstName, form.LastName)
LIKE :srchValue");
$stmt->execute($searchArray);
$records = $stmt->fetch();
$totalRecordwithFilter = $records['allcount'];
Fetch records
$sql = "SELECT form.FirstName, form.LastName, form.PrisonerID, HistoryLog.*
FROM form
LEFT JOIN HistoryLog
ON form.CR_ID = HistoryLog.CR_ID
WHERE CONCAT(form.FirstName, ' ', form.LastName)
LIKE :srchValue
OR CONCAT_WS(form.CR_ID, form.PrisonerID,form.FirstName, form.LastName)
LIKE :srchValue
ORDER BY '.$columnName.' '.$columnSortOrder.' LIMIT :limit,:offset";
// ORDER BY HistoryLog.Rec_Date DESC LIMIT :limits,:offset";
$stmt = $dbh->prepare($sql);
// Bind values
foreach($searchArray as $key=>$search){
$stmt->bindValue(':'.$key, $search,PDO::PARAM_STR);
}
// $stmt->bindValue(':srchValue',$searchValue, PDO::PARAM_INT);
$stmt->bindValue('limit', (int)$row, PDO::PARAM_INT);
$stmt->bindValue('offset', (int)$rowperpage, PDO::PARAM_INT);
$stmt->execute();
$userRecords = $stmt->fetchAll();
$data = array();
foreach($userRecords as $row){
$data[] = array(
"Rec_Date"=>$row['Rec_Date'],
"firstName"=>$row['FirstName'],
"lastName"=>$row['LastName'],
"prisonerid"=>$row['PrisonerID'],
"ContactType"=>$row['Contact Type'],
"entryBy"=>$row['entryBy'],
"CR_ID"=>$row['CR_ID'],
"SF_ID"=>$row['SF_ID'],
"Comments"=>$row['Comments']
);
}
Response
$response = array(
"draw" => intval($draw),
"iTotalRecords" => $totalRecords,
"iTotalDisplayRecords" => $totalRecordwithFilter,
"aaData" => $data
);
echo json_encode($response);
<?php > ``` ?>This question has an accepted answers - jump to answer
Answers
Hi,
You are using server-side processing there, so your PHP would need to be modified to suit your need (since sorting is done in the server-side here).
First question is, do you need server-side processing? Do you have tens of thousands of rows?
If you do need it, what to do would be to write an SQL query that will sort the data as you need it and then reserve engineer it so your PHP can generate that. At least, that's how I would approach the problem
.
Allan
But is it really being sorted on the server side?
The sort comes from the datatables call:
The records pulled in this query are 10,464 and grow with each contact, so yeah sorting is more important here. but I only need it in descending order. I just need it sorted showing the most recent entry first. I do not have this problem in my other datatables, but if I remove the call above, and replace it with my own server-side sort "see the commented code listed in question" datatables gives me an empty table.
So yes, the requirement is that the server-side must perform whatever ordering it is that you need.
If I understand correctly, the uncommented one returns data, but not in the sort order you want. The commented one gives an empty return (which would suggest a PHP / SQL error)?
What does
$columnNameresolve to (you could echo it out to an error log to check it). Also be super careful there - you've got a bunch of SQL injection attacks possible - e.g. if they modified the submit for the sorting column name or direction.Allan
Ok, this line is going nowhere. Those values come from the datatables function. Why they don't work with a MySQL Join query, is what I do not know. That is the reason for this post. If I need to use a different methodology for join tables with datatables, I am asking that in this post. I can run this query and sort it fine without datatables. Again, I am trying to use datatables. This works great for other MySQL queries, just not for this join. Sorting is coming from the datatables function. Manipulating it comes from the datatables function through the posts above. Did I make this clear enough? I added the code in hopes of someone seeing an obvious coding or methodology error for MySQL joins.
Question, do I need to rewrite my code using the SSP scripts @Allen has in github? in order to make he join query work with datatables?
I think I found it. I'll do a rewrite according to https://editor.datatables.net/manual/php/joins#Server-side and see if that works.
well, that didn't work, feel kind of stupid donating to support a system that can't do joins. Bye all!
Server-side processing with joins can be quite difficult, which is why our demo PHP script doesn't support it, and your custom script above doesn't appear to either. Editor's however, as you've seen, does. Let me know how you get on with it.
Happy to refund your "supporter" donation if you'd like?
Allan
Thanks, I was so frustrated, I went to my office got our little non-profit's credit card, and bought an editor license. Doing it with your library works, pretty well actually. Little miffed about the price for 'open-source' code, but I'm just happy it works. Will no doubt ask questions later if I can't figure something else out.
I think there might be a little confusion - sorry. Editor (client-side) has a commercial license, but the server-side libraries are MIT licensed open source (license).
If you aren't planning to use the client-side Editor software, let me know and I'll refund that payment.
Allan
It's okay, if I can customize the editor modal enough, I will use it client-side. Working through that now.
Super - thank you. Shout if you run into any problems with it.
Allan