/** * 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; }