Php filter function for jquery table
I am using jquery datatables in server side processing and I am trying to implement in php a function for my Global filter:
The function should display all lines only if and only if :
- both words are in the same column AND in the same row
OR
- both words are in different columns AND in the same row
So. If I type in floor free
or free floor
in the Global Filter, it should always return the MOST request.
You can test the global filter here, but this table is not loading data from MySQL and I want to replicate this client side filtering in php: DEMO
Instead, in my server side table, the corresponding php code that controls the global filter is:
static function filter ( $request, $columns, &$bindings )
{
$globalSearch = array();
$columnSearch = array();
$dtColumns = self::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 = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
//$globalSearch[] = "match(".$column['db'].") against (".$binding.")";
$globalSearch[] = "`".$column['db']."` LIKE ".$binding;
}
}
}
What should I do?
I believe I will blow up the search string into separate words:
$words = explode(' ', $str);
$wordcount = count($words);
and then my state should look like this:
WHERE IF(Column1 LIKE '%{$words[0]}%' OR Column2 LIKE '%{$words[0]}%' OR Column4 LIKE '%{$words[0]}%', 1, 0)
+ IF(Column1 LIKE '%{$words[1]}%' OR Column2 LIKE '%{$words[1]}%' OR Column4 LIKE '%{$words[1]}%', 1, 0)
+ IF(Column1 LIKE '%{$words[2]}%' OR Column2 LIKE '%{$words[2]}%' OR Column4 LIKE '%{$words[2]}%', 1, 0)
+ ...
>= $wordcount
Each of the IF expressions must be 1 if the word is found in any of the columns. Adding them together will result in the total number of words found in any column. Then check if it is at least the number of words (it can be more if the same word is in multiple columns).
But I don't understand how to use nested foreach loops to create a request from your input. Also I don't understand If I have to use Table API
source to share
Hey, I think I have an idea. But be careful, this is untested. The function should var_dump the SQL statement like this:
SELECT * FROM table
WHERE
CONCAT(column1, column2, column3) LIKE '%free%' AND
CONCAT(column1, column2, column3) LIKE '%floor%'
Concat joins 3 given columns to one (you can do as many columns as you like)
function filter ( $request, $columns, &$bindings )
{
$globalSearch = array();
$columnSearch = array();
$dtColumns = self::pluck( $columns, 'dt' );
if ( isset($request['search']) && $request['search']['value'] != '' ) {
$string = $request['search']['value'];
$string = split(" ", $string);
$query = "SELECT * FROM table WHERE ";
$j = 0;
foreach($string as $str){
$concat = "CONCAT(";
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 = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
//$globalSearch[] = "match(".$column['db'].") against (".$binding.")";
$globalSearch[] = $column['db'];
}
}
$concat .= implode(",", $globalSearch) . ") LIKE '%" . $str . "%'";
if($j > 0 ){
$query .= " AND " . $concat;
}else{
$query .= $concat;
}
$globalSeach = array();
$j++
}
var_dump($query);
}
}
Hope my code helps you a little.
source to share