How can I create filter in server side processing?

kviktor1230

Hi,
I'm trying to create a filter in DataTables, but what I found is only filtering the data in "front end" (in the datatables script). I have 10K rows in the SQL table so I think, the "front end filtering/searching" is not my best option. I need to create a filter to my SQL Query in server-side, and get back only the filtered rows (datas).

Also the search option is not good option for me because I have in tables values like 1 or 2 (boolean).

My DataTables using this method (way) of fetching datas from SQL in backend:

include 'config.php';

/// Read value
$draw = $_POST['draw'];
$row = $_POST['start'];
$rowperpage = $_POST['length']; // Rows display per page
$columnIndex = $_POST['order'][0]['column']; // Column index
$columnName = $_POST['columns'][$columnIndex]['data']; // Column name
$columnSortOrder = $_POST['order'][0]['dir']; // asc or desc
$searchValue = $_POST['search']['value']; // Search value

$searchArray = array();

/// Search 
$searchQuery = " ";
if($searchValue != ''){
   $searchQuery = " AND (emp_name LIKE :emp_name or 
        email LIKE :email OR 
        city LIKE :city ) ";
   $searchArray = array( 
        'emp_name'=>"%$searchValue%", 
        'email'=>"%$searchValue%",
        'city'=>"%$searchValue%"
   );
}

/// Total number of records without filtering
$stmt = $conn->prepare("SELECT COUNT(*) AS allcount FROM employee ");
$stmt->execute();
$records = $stmt->fetch();
$totalRecords = $records['allcount'];

/// Total number of records with filtering
$stmt = $conn->prepare("SELECT COUNT(*) AS allcount FROM employee WHERE 1 ".$searchQuery);
$stmt->execute($searchArray);
$records = $stmt->fetch();
$totalRecordwithFilter = $records['allcount'];

/// Fetch records
$stmt = $conn->prepare("SELECT * FROM employee WHERE 1 ".$searchQuery." ORDER BY ".$columnName." ".$columnSortOrder." LIMIT :limit,:offset");

// Bind values
foreach($searchArray as $key=>$search){
   $stmt->bindValue(':'.$key, $search,PDO::PARAM_STR);
}

$stmt->bindValue(':limit', (int)$row, PDO::PARAM_INT);
$stmt->bindValue(':offset', (int)$rowperpage, PDO::PARAM_INT);
$stmt->execute();
$empRecords = $stmt->fetchAll();

$data = array();

foreach($empRecords as $row){
   $data[] = array(
      "emp_name"=>$row['emp_name'],
      "email"=>$row['email'],
      "gender"=>$row['gender'],
      "salary"=>$row['salary'],
      "city"=>$row['city']
   );
}

/// Response
$response = array(
   "draw" => intval($draw),
   "iTotalRecords" => $totalRecords,
   "iTotalDisplayRecords" => $totalRecordwithFilter,
   "aaData" => $data
);

echo json_encode($response);

In this code as you can see I have Search option, but as I said I can't use it for filtering columns with boolean values.

Another example what I want to do:

I have a column named by "edited" with boolean values. How can I get/filter those rows where the column "edited" have values 0?

I'm using MariaDB.

Thank you for your help!

allan

Hi,

I'm presuming that there is something a little more complex needed than just edited = 0 in your WHERE condition, but I'm not clear on what that would be.

Is the search for the edited=0 static, or is it dependent on client-side input? If it needs client-side input, what is that input?

Thanks,
Allan