Malfunction on serverside search ( example given )

Samu2nd

Hey Guys.
the last two years i used Datatables with an textbased database, but I now have switched to a
server-side version of Datatables with an mysql database and I noticed a strange malfunction.

If you try to search for a word beginning with 0 _ % then nothing will happen.
Datatables will not perform a search

At first I thought there is just a mistake in my script, but I noticed that the same problem also occurs
on the official Datatables.net page in this example.

https://datatables.net/examples/data_sources/server_side

If you try it in the next example ( not serverside )
it will work !

https://datatables.net/examples/basic_init/zero_configuration.html

Does anyone have information about this?
What can i do to fix it ?

Thx Samu

kthorngren

If you try to search for a word beginning with 0 _ % then nothing will happen. Datatables will not perform a search

I tried the global sarch with 0 _ % in the server side example and I can see the server side request sent to the server. Maybe Im' misunderstanding how to replicate the issue.

Kevin

Samu2nd

Yes i think you misunderstood it.

Open the following Link and sarch this _ Sign
https://datatables.net/examples/basic_init/zero_configuration.html

Then open this Link and try the same
https://datatables.net/examples/data_sources/server_side

It's the same Data in both Tables but not the same search result !
Do you now understand my Problem ?

Samu

kthorngren

I see, you mean to search for _. Just the underscore. According to the MySql Pattern Matching docs underscore is a wildcard. The same page mentions using regex searches. You could try that.

I don't use the Datatables server side libraries so not sure if the MySql code supports regex searches but if you are using them you might want to try it. Try with the search() API and if it works then you can create your own search input or you can create your own event for the default global search like this example:
http://live.datatables.net/jorexujo/1/edit

Another option you can try is to escape the _ as shown in this SO thread.

Kevin

Samu2nd

Ok,
that explains the behavior for _ and %.
But i have the same problem with 0
What is the explanation for that.

Samu

kthorngren

Searching for 0 is working here:
https://datatables.net/examples/data_sources/server_side

Your server side script is responsible for searches. Are you using Datatables provided scripts?

Kevin

Samu2nd

Hi Kevin,

this is the PHP part of my test script

<?php
/* Database connection start */
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test2";

$conn = mysqli_connect($servername, $username, $password, $dbname) or die("Connection failed: " . mysqli_connect_error());

/* Database connection end */


// storing  request (ie, get/post) global array to a variable  
$requestData= $_REQUEST;


$columns = array( 
// datatable column index  => database column name
    0 =>' mydata1'
);




// getting total number records without any search
$sql = "SELECT mydata1 ";
$sql.=" FROM noname";
$query=mysqli_query($conn, $sql) or die("mytable-data.php: get nonames");
$totalData = mysqli_num_rows($query);
$totalFiltered = $totalData;  // when there is no search parameter then total number rows = total number filtered rows.




$sql = "SELECT mydata1  ";
$sql.=" FROM noname WHERE 1 = 1";

// getting records as per search parameters
if( !empty($requestData['columns'][0]['search']['value']) ){   //name
    $sql.=" AND mydata1 LIKE '%".$requestData['columns'][0]['search']['value']."%' ";
}


$query=mysqli_query($conn, $sql) or die("mytable-data.php: get nonames");
$totalFiltered = mysqli_num_rows($query); // when there is a search parameter then we have to modify total number filtered rows as per search result.

$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']."   LIMIT ".$requestData['start']." ,".$requestData['length']."   ";  // adding length

$query=mysqli_query($conn, $sql) or die("mytable-data.php: get nonames");




$data = array();
while( $row=mysqli_fetch_array($query) ) {  // preparing an array
    $nestedData=array(); 

    $nestedData[] = $row["mydata1"];

    $data[] = $nestedData;
}



$json_data = array(
            "draw"            => intval( $requestData['draw'] ),   // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw. 
            "recordsTotal"    => intval( $totalData ),  // total number of records
            "recordsFiltered" => intval( $totalFiltered ), // total number of records after searching, if there is no searching then totalFiltered = totalData
            "data"            => $data   // total data array
            );

echo json_encode($json_data);  // send data as json format

?>