Server-Side Regex search and utf-8 char support is now available

Server-Side Regex search and utf-8 char support is now available

netexpnetexp Posts: 2Questions: 0Answers: 0
edited August 12 in DataTables

Hello everybody!

With some small changes, I made it possible to search with support of regex in Server-Side mode, and also supporting of Persian and Arabic languages and generally UTF-8, is available in result of my changes.

By replacing the following functions with same named old function already there in the ssp.class.php file, you will be able to use changes described.
You can now search in your database by placing regex symbols in Column and Global search inputs.
Also, Persian and UTF-8 characters will be displayed correctly.

    static function sql_connect ( $sql_details )
    {
        try {
            $db = @new PDO(
                "mysql:host={$sql_details['host']};dbname={$sql_details['db']}",
                $sql_details['user'],
                $sql_details['pass'],
                array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION )
            );
            $db->exec("SET NAMES 'utf8'");
            $db->exec("SET CHARACTER SET 'utf8'");
            $db->exec("SET character_set_connection = 'utf8'");
        }
        catch (PDOException $e) {
            self::fatal(
                "An error occurred while connecting to the database. ".
                "The error reported by the server was: ".$e->getMessage()
            );
        }

        return $db;
    }




    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'];
            $regexp = $request['search']['regex'];

            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'])){
                        if($regexp){
                            $binding = self::bind( $bindings, $str, PDO::PARAM_STR );
                            $globalSearch[] = "`".$column['db']."` REGEXP ".$binding;
                        }
                        else {
                            $binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
                            $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];
                $regexp = $requestColumn['search']['regex'];
                $columnIdx = array_search( $requestColumn['data'], $dtColumns );
                $column = $columns[ $columnIdx ];

                $str = $requestColumn['search']['value'];

                if ( $requestColumn['searchable'] == 'true' &&
                 $str != '' ) {
                    if(!empty($column['db'])){

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

        return $where;
    }

Replies

  • colincolin Posts: 12,600Questions: 0Answers: 2,154

    Nice, thanks for sharing,

    Colin

  • netexpnetexp Posts: 2Questions: 0Answers: 0

    Thank you Colin

Sign In or Register to comment.