Case insensitive search using server-side processing?

projectprogramamark

Hi,

I'm currently trying to implement case insensitive search on my Datatables project. I've got server-side processing set to true, and I'm using the example server side PHP script and the complex function on ssp.class.php. I'm not sure how to make it such that case insensitive search is set by default. I'm using a MySQL server, and have tried replacing the LIKE statements in the filter function such that it is "LIKE BINARY," but neither that nor "ILIKE" are working. Is there a way to do this? Can post code if needed, but it's pretty similar to the examples shown in the documentation

kthorngren

Have you tried LOWER()?

Kevin

projectprogramamark

Hi @kthorngren sorry for the extremely late response, I had switched over to a different project for a bit.

I've thought about using LOWER(), but I'm not sure exactly where to put it. I've pasted the relevant code below from ssp.class.php, and put comments where I think LOWER() should go, but it's presenting me with errors and debugging is rather vague when it comes to this.

    static function filter ( $request, $columns, &$bindings )
    {
        $globalSearch = array();
        $columnSearch = array();
        $dtColumns = self::pluck( $columns, 'dt' );

        if ( isset($request['search']) && $request['search']['value'] != '' ) {
            // LOWER HERE?
            $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' ) {
                    if(!empty($column['db'])){
                        $binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
                        // LOWER HERE?
                        $globalSearch[] = "`".$column['db']."` LIKE ".$binding;
                    }
                }
            }
        }

        // 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 ];

                // LOWER HERE?
                $str = $requestColumn['search']['value'];

                if ( $requestColumn['searchable'] == 'true' &&
                 $str != '' ) {
                    if(!empty($column['db'])){
                        $binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
                        // LOWER HERE?
                        $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;
        }

        return $where;
    }

projectprogramamark

@allan maybe you'd be able to help with this as well?

allan

Changing LIKE to ILIKE in the two lines where it is used in that code should be enough with MySQL. Actually, many MySQL databases use a case-insensitive collation anyway (which can be seen to be the case here). What collation are you using?

Allan

projectprogramamark

I've tried ILIKE but I get a syntax error. I'm using MySQL and I believe ILIKE is limited to PostgresSQL. I thought the default was to be case insensitive, but my search is being case sensitive and I'm not sure what else could be causing it besides this

projectprogramamark

I'm not 100% sure which collation I'm using, but if I try and specify the collation (for example, COLLATE utf8_general_ci, it returns Syntax error or access violation: 1253 COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1', so I'm assuming the collation being used for the search is latin1? Sorry, I'm not too experienced with MySQL

projectprogramamark

I've figured it out now. I just added the latin1_swedish_ci collation instead and it seemed to do the trick. Should probably move away from latin1 and to utf8 in general though. Thanks for all of the help!

kthorngren

You can use one of these queries to get the collation:
https://stackoverflow.com/questions/3832056/mysql-check-collation-of-a-table

I'm not familiar with PHP but would guess to use the LOWER() function like this:

$globalSearch[] = "`".$column['db']."` LIKE LOWER(".$binding.")";

Kevin