Disable specific column search in server-side processing (MySQL)

santycg

Hi!

I'm using DataTables with server-side processing from a remote MySQL database. There is a ID column I need to get/locate actual record IDs, but don't want end-users to search by that column.

For example, if I write '123' in the datatables search input, it filters and shows records with that ID (or even IDs with '1', '12', and '123'), but IDs are internal values end-users don't need to know or see.

I tried to assign the 'searchable: false' to that column but it seems it's not working at all with server-side processing when retrieving data from a mysql database. All I can do is disable searching in the entire table.

I also tried to set searching:false to the datatable and then searchable:true just to some columns, with no effect.

So, is it possible to disable searching to specific columns in server-side processing a remote mysql table?

Thanks!

rf1234

I can reassure you that it works. You probably have a different problem in your code - which we can't see ...

This is from my own coding from two data tables with serverSide: true.
In my HTML I assigned the class "noSearch" to certain columns <th class="noSearch">. This class can be used as a target in columnDefs. The variable "searchable" is either true or false depending on the page the data table is opened in.

columnDefs: [
   // targets may be classes
    {   targets: "noSearch", searchable: searchable }
],

And a simpler example:

columnDefs: [
    // targets may be classes
    {targets: [0, 1, 2, 3], searchable: false}
],

santycg

This is my table structure:

<table id="myTable" class="table table-striped table-sm table-hover compact"> <thead> <tr> <th data-field="id" align=center><center>ID</th> <th data-field="code" align=center><center>Ccode</th> <th data-field="name">Name</th> </tr> </thead> </table>
This is my table initialisation:

`
var table=$("#myTable").DataTable(
{
"processing": true,
"serverSide": true,
"ajax": {
"url": "gettabledata.php",
"type": "POST"
}
},
columnDefs:[
{ targets : 0, searchable : false } //ID Field
]
});

`

Remember I'm using 'ssp.class.php' and 'gettabledata.php' to retrieve the data from the MySQL server.

I'm using Datatables 1.10.20

santycg

If I disable searching for another column (Code, for example), then it works fine and I can't search by code.

But if I disable searching for ID column, it doesn't work and I can still search by ID.

I really don't know what's going on

santycg

This doesn't work with my Table:

"columnDefs":[ { "targets": 0,"searchable": false } ]

However, this works:

"columns": [ { "searchable": false }, { "searchable": true }, { "searchable": true } ],

and I don't know why.

colin

We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

santycg

Sorry, but I'm afraid I can't create a test case with server processing. I tried and I got the following error:

origin 'http://live.datatables.net' has been blocked by CORS policy: No 'Access-Control-Allow-Origin' header is present on the requested resource

All I can do is create a simple test page where you can see the problem. I will try to create it and post it in a few minutes.

kthorngren

You can use one of the base server side JS BIN scripts from here:
https://datatables.net/manual/tech-notes/9#Server-side-processing

Kevin

santycg

I found the problem!

I'm using the last column to show a button in each cell, so I use this line to add it:

{ targets: -1,orderable: false, defaultContent: buttoncode },

if I delete that line, now the searchable:false works perfect!

So I noticed that, maybe, that last auto-generated column contains something like an internal row id as a unique identifier.

If I change it to...

{ targets: -1,orderable: false,** searchable: false**, defaultContent: buttoncode },

Then the search is working perfect!

I didn't know that 'empty' column contains the same ID as the ID column.

Thanks btw. Rewritting the code while preparing the test page did the trick