How to get server-side searching working?

DarkWhoppy

I recently converted a table from a normal DataTable to a server-side DataTable generated by PHP. The table is generated with all the correct data. Sorting works perfectly fine. I can sort by any column. If I recall correctly, when using server-side processing, sorting and filtering uses the same functions to re-query the data server-side.

For some strange reason, the search box always returns zero rows. It worked perfectly fine as a non server-side datatable. Has anyone else seen this? I'm using the latest version. (1.10.11)

The other file is the ssp.class.php file included with the source. With the help of this post, I was able to connect to my SQL 2014 server: https://www.datatables.net/forums/discussion/32123/server-side-processing-with-pdo-mssql

It feels like I'm missing a flag or something. But even when I set searching to true, it doesn't work.

$(document).ready(function() {
     $('#da_table').DataTable( {
          "processing": true,
          "serverSide": true,
          "ajax": "proc.php",
          "order": [[ 1, "desc"]]
     });
});

allan

I would suggest echoing out the SQL that is being used to query the database. I can only assume that it is creating something that is returning 0 records, so that would be the starting point to see exactly what it is doing.

Allan

DarkWhoppy

Here's the WHERE string echoed from the JSON request:

WHERE('name' LIKE:binding_0 OR 'type' LIKE :binding_1 OR 'owner' LIKE :binding_2 OR 'updated' LIKE :binding_3)

The person that posted the MS SQL modifications did not modify the filter function. I'm assuming there is something about the way the WHERE string is constructed that's preventing SQL Server from returning results. I'm just not that familiar with the differences between MySQL and SQL Server to know where the problem lies.

DarkWhoppy

Ha! I figured out which piece of the code needed to be changed to support SQL Server. I can now perform searches.

//$globalSearch[] = "`".$column['db']."` LIKE ".$binding;
$globalSearch[] = "[".$column['db']."] LIKE ".$binding;

...
//$columnSearch[] = "`".$column['db']."` LIKE ".$binding;
$columnSearch[] = "[".$column['db']."] LIKE ".$binding;

tfrancois

@DarkWhoppy,

Thank you for submitting your fix!!!! This issue was giving me fits until I came across your fix and it worked PERFECTLY! Truly a life-saver. Thank you sir!