Server side column filter not filtering value displayed in the table

DudtheDood

Hi all,

I was able to successfully enable datatable server side processing by following the code here https://www.datatables.net/examples/data_sources/server_side.html and applied column filter using the code here https://datatables.net/examples/api/multi_filter.html.

The filter works but it does not filter the value that is displayed in the table. So for example I have a status_id (Foreign key) column in my test table (the one being displayed by datatable). I also have status table in the database that has id and status_name which displays the name of the status (i.e. id 1 status_name excellent, id 2 status_name good, id 3 status_name bad). When I fill in the column filter input field with the status name i.e. good/bad/excellent it doesn't show anything, but if I put in the number 1 it shows all records that have the status excellent since status excellent id is 1.

In my server_processing.php I have this for the status column to change the value displayed in the status column in datatable (display status name instead of the id)

$columns = array(
    array(
        'db'        => 'status_id',
        'dt'        => 0,
        'formatter' => function( $d, $row ) {
            $sql = "SELECT name FROM status WHERE id = '$d'";
            $conn2 = new mysqli("localhost", "root", "password", "db");
            if ($conn2->connect_error) {
               die("Connection failed: " . $conn2->connect_error);
            }
            $result = $conn2->query($sql);
            $row = $result->fetch_array();
            $statusname = $row["status_name"];
            return $statusname;
        }
    )
);

How to make the filter so it filters based on the value displayed in the datatable?

lenap

Hello,
Same here, filter use the original value .. Note the foramatted value....

lenap

Any ideas ?

glenderson

I'm not using PHP so I have to write all the server side based queries myself without using any of the provided scripts.

In doing such, when a user enters a value into the search filter, it triggers the ajax event. In my ajax page, I need to pull the &search value from the request URL. So my SQL is constructed based upon whether I have a &search value or not.

if search.length
SQL = "Select * from someTable Where myFields like '%search value%'

if no search.length
SQL = "Select * from someTable'

It's actually much more complex than that because I search for partial words, whole words, break chars, words across multiple columns, etc.

Same sort of thing happens for sorting columns, that also fires the tables ajax event and provides the columns that are selected for sorting and their order. But you must perform the actual ordering in your sql statement.

When I use server side process, I do everything, dataTables just triggers the ajax event, and I pull everything from the request URL. You must also calculate the total number of records, the starting page, etc.