Regex in column search not working

MarkvdB

Hi!

I try the following Regex to filter multiple values in a single column:

$datatable.columns( 3 ).search('Samsung S9|Samsung S8',true, false).draw();

In the example https://datatables.net/examples/api/regex.html I get the pipe operator working, why not in my example?
When I remove "|Samsung S8" the columns get nicely filtered on "Samsung S9".

colin

Hi MarkvdB,

Yep, that's right, take a look at this live example!

What happens for you when you specify both phones? Are you getting no search hits? Or just 'Samsung S8'?

Cheers,

Colin

MarkvdB

'Samsung S9 Samsung S8' = no hits
'Samsung S9' = 10 hits // correct
'Samsung S8' = 3 hits // correct

'Samsung S9 Samsung S8' = (smart search) no hits
'Samsung S9|Samsung S8' = (smart search) no hits

colin

Could you create a live example, like my one? As my example shows, the hits are right, so there must be something odd going on with your code.

MarkvdB

hmm strange. Can it be a issue that I get the results via ajax serverside?

Edit:

I do think so:

> if ( isset( $request['columns'] ) ) {
>           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 = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
>                   $columnSearch[] = "`".$column['db']."` LIKE ".$binding;
>               }
>           }
>       }

MarkvdB

Some work in progress, any tips?

$exploded = explode('|',$str);
                foreach($exploded as $key => $string)
                {
                    if ( $requestColumn['searchable'] == 'true' &&  $string != '' ) {
                        $binding = self::bind( $bindings, '%'.$string.'%', PDO::PARAM_STR );
                        $columnSearch[] = "`".$column['db']."` LIKE ".$binding;
                    }
                }

colin

Yep, it must be something to do with how your server-side script is handling that search request. My PHP isn't strong enough yet to be able to help with that I'm afraid, but hopefully somebody else could take a look.

C

MarkvdB

Does anyone have a complete PHP serverside script capable of doing multiselects?

MarkvdB

Current working concept:

Add:
$multiSearch = array();

Replace function with:

    // Individual column filtering
            if ( isset( $request['columns'] ) ) {
                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'];

                    $exploded = explode('|',$str);
                    $cnt = count($exploded);
                    foreach($exploded as $key => $string)
                    {
                        if ( $requestColumn['searchable'] == 'true' &&  $string != '' ) {
                            $binding = self::bind( $bindings, '%'.$string.'%', PDO::PARAM_STR );
                            if($cnt == 1)
                            {
                                $columnSearch[] = "`".$column['db']."` LIKE ".$binding;
                            }else{
                                $multiSearch[] = "`".$column['db']."` LIKE ".$binding;
                            }

                        }
                    }
                    /*
                    if ( $requestColumn['searchable'] == 'true' &&
                     $str != '' ) {
                        $binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
                        $columnSearch[] = "`".$column['db']."` LIKE ".$binding;
                    } 
                    */
                }
            }

Add:

        if ( count( $multiSearch ) ) {
                    $where = $where === '' ? implode(' OR ', $multiSearch) : $where .' AND '. implode(' OR ', $multiSearch);
                }

MarkvdB

New fully working code:

`// Individual column filtering
if ( isset( $request['columns'] ) ) {
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'];

            $exploded = explode(',',$str);
            $cnt = count($exploded);
            foreach($exploded as $key => $string)
            {
                if ( $requestColumn['searchable'] == 'true' &&  $string != '' ) {
                    $binding = self::bind( $bindings, ''.$string.'', PDO::PARAM_STR );
                    if($cnt == 1)
                    {
                        $columnSearch[] = "`".$column['db']."` = ".$binding;
                    }else{
                        $multiSearch[$column['db']][] = "`".$column['db']."` = ".$binding;
                    }
                    
                }
            }
            /*
            if ( $requestColumn['searchable'] == 'true' &&
             $str != '' ) {
                $binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
                $columnSearch[] = "`".$column['db']."` LIKE ".$binding;
            } 
            */
        }
    }

if ( count( $multiSearch ) ) {
foreach($multiSearch as $t)
{
$imploded[] = implode(' OR ', $t);

        }
        if($where === '')
        {
            $where = '('.implode(') AND (', $imploded).')';
        }else{
            $where = '('.$where.') AND ('.implode(') AND (', $imploded).')';
        }
    }`