datatables + serverside processing + search filtering

Question

I am following this tutorial:
http://coderexample.com/datatable-demo-server-side-in-phpmysql-and-ajax/

and here is the demo:
http://coderexample.com/demo/datatable-demo-server-side-in-phpmysql-and-ajax/

If i search for ou in the search input i get No matching records found but what I would like returned is this row Airi Satou at least.

this is the code that i have to change, I think, as I have to do the search server side.

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

$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 =>'employee_name', 
    1 => 'employee_salary',
    2=> 'employee_age'
);

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


if( !empty($requestData['search']['value']) ) {
    // if there is a search parameter
    $sql = "SELECT employee_name, employee_salary, employee_age ";
    $sql.=" FROM employee";
    $sql.=" WHERE employee_name LIKE '".$requestData['search']['value']."%' ";    // $requestData['search']['value'] contains search parameter
    $sql.=" OR employee_salary LIKE '".$requestData['search']['value']."%' ";
    $sql.=" OR employee_age LIKE '".$requestData['search']['value']."%' ";
    $query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees");
    $totalFiltered = mysqli_num_rows($query); // when there is a search parameter then we have to modify total number filtered rows as per search result without limit in the query 

    $sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']."   LIMIT ".$requestData['start']." ,".$requestData['length']."   "; // $requestData['order'][0]['column'] contains colmun index, $requestData['order'][0]['dir'] contains order such as asc/desc , $requestData['start'] contains start row number ,$requestData['length'] contains limit length.
    $query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees"); // again run query with limit

} else {    

    $sql = "SELECT employee_name, employee_salary, employee_age ";
    $sql.=" FROM employee";
    $sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']."   LIMIT ".$requestData['start']." ,".$requestData['length']."   ";
    $query=mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees");

}

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

    $nestedData[] = $row["employee_name"];
    $nestedData[] = $row["employee_salary"];
    $nestedData[] = $row["employee_age"];

    $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

?>

Am I right to say that this is the code I have to change?
If so, can anyone advise what I have to do?
I understand this is alot to ask but would appreciate the guidance!

score 3 · Accepted Answer · 2016-04-26T04:19:19.780

3

 $sql.=" WHERE employee_name LIKE '".$requestData['search']['value']."%' ";

will match search term then anything (due to wild card %)

as you want to match the search term in the middle of the name you need to add the wild card at the begging as well:

 $sql.=" WHERE employee_name LIKE '%".$requestData['search']['value']."%' ";

Note this will disable the use of indexes on employee_name which may or may not be a problem for you.

This is not the best search approach over all, you should not check all three fields, but ask the searcher which to use. After all age and salary could have some matching numbers.

a search for 27, could match age 27 or 27000 salary etc. And no one will have an age of bob so its pointless to do that search

edited Apr 26 '16 at 04:19

answered Apr 26 '16 at 04:14

Don't think indexes will matter in this instance but good point. I am working towards a search per column but am haiving difficulty. question I asked [here](http://stackoverflow.com/questions/36706424/datatables-connecting-to-a-db-getting-data-but-some-features-not-working). maybe you coul point me in the right direcition? And I have to do the same for each column e.g. search `27` for this salary `162700` to show up. – HattrickNZ Apr 26 '16 at 20:40
@HattrickNZ i'm not sure from the comment if this question is now answered or not – Apr 26 '16 at 22:57

score 1 · Answer 2 · answered Apr 26 '16 at 04:15

Your query need to be updated.

The following SQL statement selects all with a employee_name starting with "search field value":

   $sql.=" WHERE employee_name LIKE '".$requestData['search']['value']."%' ";

so, your query should be something like below, to get desired output, as it check whether a particular pattern is present in the employee_name.

   $sql.=" WHERE employee_name LIKE '%".$requestData['search']['value']."%' ";

score 1 · Answer 3 · edited May 23 '17 at 12:08

1

The way you are constructing your Where clause, the fields you are searching must start with the search term. Change your where clauses to

$sql.=" WHERE employee_name LIKE '%".$requestData['search']['value']."%' ";

You are also open to SQL injection by doing what you are doing. You need to use parameterized queries to remove this vulnerability. See How can I prevent SQL injection in PHP? for information

edited May 23 '17 at 12:08

Community

1
1

answered Apr 26 '16 at 04:16

randyh22

463
4
10

tks same as dagon above. and tks for sql injection link – HattrickNZ Apr 26 '16 at 20:40

datatables + serverside processing + search filtering

3 Answers3