CODEWITHSUNDEEP
javascriptphpjquerymysqldatatables
HattrickNZ
HattrickNZ

Reputation: 4643

datatables + column search + overall search working in conjunction + serverside processing

I am following this tutorial: http://coderexample.com/datatable-custom-column-search/

and here is the demo: http://coderexample.com/demo/dataTable-custom-column-search/

Note in the demo the search input is removed/hidden.
Here is a working FIDDLE but this is not a serverside example but the column search and overall search work in conjunction for the test data that is there.

here is the code that does the filtering per column 

## Columns Search 
$sql = "SELECT employee_name, employee_salary, employee_age  ";
$sql.=" FROM employee WHERE 1 = 1";

// getting records as per search parameters
if( !empty($requestData['columns'][0]['search']['value']) ){   //name
    $sql.=" AND employee_name LIKE '%".$requestData['columns'][0]['search']['value']."%' ";
}
if( !empty($requestData['columns'][1]['search']['value']) ){  //salary
    $sql.=" AND employee_salary LIKE '%".$requestData['columns'][1]['search']['value']."%' ";
}
if( !empty($requestData['columns'][2]['search']['value']) ){ //age
    $sql.=" AND employee_age LIKE '%".$requestData['columns'][2]['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.

$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("employee-grid-data.php: get employees");

here is the code that does the filtering per overall search input: 

// if there is a search parameter
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 // to search for ou to get satou
    $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");

}

what I would like to do is get all the column searches and the overall search working in conjunction, as per the fiddle example above (e.g. o in the employee name search and 2 in the overall search).

I would have thought it is something to do with the if statements. I have tried various permutations of this, but think there might be more to it. Can anyone advise? 

if( !empty($requestData['search']['value']) ) {
    //code here
}
if( !empty($requestData['columns'][0]['search']['value']) ){   //name
    //code here
}
if( !empty($requestData['columns'][1]['search']['value']) ){  //salary
    //code here
}
if( !empty($requestData['columns'][2]['search']['value']) ){ //age
    //code here
}

EDIT1

Here is my complete code I am having difficulty in where to place the different bits of @Ruslan Osmanov's answer. Can you advise? I have tried but I am getting 500 (Internal Server Error) so maybe it something to do with the order I am connectong to the DB?

<?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.

//*  this column search works but not the overall search

## Search input

// if there is a search parameter
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 // to search for ou to get satou
    $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");

}


## Columns Search 
$sql = "SELECT employee_name, employee_salary, employee_age  ";
$sql.=" FROM employee WHERE 1 = 1";

// getting records as per search parameters
if( !empty($requestData['columns'][0]['search']['value']) ){   //name
    $sql.=" AND employee_name LIKE '%".$requestData['columns'][0]['search']['value']."%' ";
}
if( !empty($requestData['columns'][1]['search']['value']) ){  //salary
    $sql.=" AND employee_salary LIKE '%".$requestData['columns'][1]['search']['value']."%' ";
}
if( !empty($requestData['columns'][2]['search']['value']) ){ //age
    $sql.=" AND employee_age LIKE '%".$requestData['columns'][2]['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.

$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("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

Upvotes: 0

Views: 803

Answers (1)

Ruslan Osmanov
Ruslan Osmanov

Reputation: 21492

I guess you have some logic errors in your SQL. I don't think you need OR, because overall search is just an extra requirement for each column:

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

$requestData = $_REQUEST;

$columns = ['employee_name', 'employee_salary', 'employee_age'];

$sql = "SELECT COUNT(*) FROM employee";
$res = mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees");
$totalData = mysqli_num_rows($res) ? mysqli_fetch_row($res)[0] : 0;

$overall_search_value = empty($requestData['search']['value']) ? null :
  mysqli_real_escape_string($conn, $requestData['search']['value']);

for ($i = 0; $i < count($columns); $i++) {
  $field = $columns[$i];

  $value = empty($requestData['columns'][$i]['search']['value']) ? null :
    mysqli_real_escape_string($conn,
      $requestData['columns'][$i]['search']['value']);

  $q = null;

  if ($value) {
    $q []= "$field LIKE '%$value%'";
  }

  if ($overall_search_value) {
    $q []= "$field LIKE '%$overall_search_value%'";
  }

  if ($q) {
    // Every column must match overall search string
    // along with the current column search.
    $q_where []= '(' . implode(' AND ', $q) . ')';
  }
}

$q_where = implode(' AND ', $q_where);
$order_by_col = $columns[$requestData['order'][0]['column']];
$order_by_dir = $requestData['order'][0]['dir'];

$sql = "SELECT employee_name, employee_salary, employee_age
FROM employee WHERE $q_where
ORDER BY $order_by_col $order_by_dir
LIMIT {$requestData['start']}, {$requestData['length']}";

$res = mysqli_query($conn, $sql) or die("employee-grid-data.php: get employees");
$totalFiltered = mysqli_num_rows($res);

$data = [];
while ($row = mysqli_fetch_assoc($res)) {
  $data[] = $row;
}

echo json_encode([
  "draw"            => intval($requestData['draw']),
  "recordsTotal"    => intval($totalData),
  "recordsFiltered" => intval($totalFiltered),
  "data"            => $data
]);

Note mysqli_real_escape_string. You have to escape everything coming into an SQL query, especially things coming from the user input.

Upvotes: 0

Related Questions