/**
* Searching / Filtering
*
* Construct the WHERE clause for server-side processing SQL query.
*
* NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here performance on large
* databases would be very poor
*
* @param array $request Data sent to server by DataTables
* @param array $columns Column information array
* @param array $bindings Array of values for PDO bindings, used in the
* sql_exec() function
* @return string SQL where clause
EDIT : added $mywhere functionality for passing initial filtering conditions
*/
static function filter($request, $columns, &$bindings, $myWhere)
{
$globalSearch = array();
$columnSearch = array();
$dtColumns = SSP::pluck($columns, 'dt');
if (isset($request['search']) && $request['search']['value'] != '') {
$str = $request['search']['value'];
for ($i = 0, $ien = count($request['columns']); $i < $ien; $i++) {
$requestColumn = $request['columns'][$i];
$columnIdx = array_search($requestColumn['data'], $dtColumns);
$column = $columns[$columnIdx];
if ($requestColumn['searchable'] == 'true') {
$binding = SSP::bind($bindings, '%' . $str . '%', PDO::PARAM_STR);
$globalSearch[] = "" . $column['db'] . " LIKE " . $binding;
}
}
}
// Individual column filtering
for ($i = 0, $ien = count($request['columns']); $i < $ien; $i++) {
$requestColumn = $request['columns'][$i];
$columnIdx = array_search($requestColumn['data'], $dtColumns);
$column = $columns[$columnIdx];
$str = $requestColumn['search']['value'];
if ($requestColumn['searchable'] == 'true' && $str != '') {
$binding = SSP::bind($bindings, '%' . $str . '%', PDO::PARAM_STR);
$columnSearch[] = "" . $column['db'] . " LIKE " . $binding;
}
}
// Combine the filters into a single string
$where = '';
if (count($globalSearch)) {
$where = '(' . implode(' OR ', $globalSearch) . ')';
}
if (count($columnSearch)) {
$where = $where === '' ? implode(' AND ', $globalSearch) : $where . ' AND ' . implode(' AND ', $globalSearch);
}
if ($where !== '') {
$where = 'WHERE ' . $where;
// add my clause
if ($myWhere !== '') {
$where .= ' AND ' . $myWhere;
}
}
if ($where == '' && $myWhere !== '') {
// add my clause
$where = 'WHERE ' . $myWhere;
}
return $where;
}
/**
* Searching / Filtering
*
* Construct the WHERE clause for server-side processing SQL query.
*
* NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here performance on large
* databases would be very poor
*
* @param array $request Data sent to server by DataTables
* @param array $columns Column information array
* @param array $bindings Array of values for PDO bindings, used in the
* sql_exec() function
* @return string SQL where clause
*/
static function filter($request, $columns, &$bindings)
{
//log_message('error','$request '.$request);
//echo($request);
$globalSearch = array();
$columnSearch = array();
$dtColumns = SSP::pluck($columns, 'dt');
if (isset($request['search']) && $request['search']['value'] != '') {
$str = $request['search']['value'];
for ($i = 0, $ien = count($request['columns']); $i < $ien; $i++) {
$requestColumn = $request['columns'][$i];
$columnIdx = array_search($requestColumn['data'], $dtColumns);
$column = $columns[$columnIdx];
if ($requestColumn['searchable'] == 'true') {
$binding = SSP::bind($bindings, '%' . $str . '%', PDO::PARAM_STR);
$globalSearch[] = "`" . $column['db'] . "` LIKE " . $binding;
}
}
}
// Individual column filtering
for ($i = 0, $ien = count($request['columns']); $i < $ien; $i++) {
$requestColumn = $request['columns'][$i];
$columnIdx = array_search($requestColumn['data'], $dtColumns);
$column = $columns[$columnIdx];
$str = $requestColumn['search']['value'];
if ($requestColumn['searchable'] == 'true' && $str != '') {
$binding = SSP::bind($bindings, '%' . $str . '%', PDO::PARAM_STR);
$columnSearch[] = "`" . $column['db'] . "` LIKE " . $binding;
}
}
// Combine the filters into a single string
$where = '';
if (count($globalSearch)) {
$where = '(' . implode(' OR ', $globalSearch) . ')';
}
if (count($columnSearch)) {
$where = $where === '' ? implode(' AND ', $columnSearch) : $where . ' AND ' . implode(' AND ', $columnSearch);
}
if ($where !== '') {
$where = 'WHERE ' . $where;
}
if (isset($request['tag'])) {
if ($where !== '') {
$where = $where . ' and tag in (' . $request['tag'] . ') ';
} else {
$where = 'WHERE tag in (' . $request['tag'] . ') ';
}
}
return $where;
}
