How can we do global search by using comma to all the columns

SandeepMurala

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:

SandeepMurala

HI ,
I am using datatable server side scripting ,In that I am using main.php and server.php and ssp.class.php ,in main.php I have written ajax by calling server.php ,in server.php file I have written all my queries with conditions ,
It is working fine as I expected ,
there is text box called ```search`` (global search),based on this I am able to do global search.

Here one thing I noticed when I do global search like 'India karnataka Bangalore'
(India-> countrie field,karnataka-> state field,Bangalore-> city field all are different fields ) I will get proper result like one record of Bangalore city , karnataka state and india countries . This is coming when I use normal datatable concept (without using server side scripting (server.php)),

But when I am using server side scripting (server.php) I have to use ,(comma) like
'India,karnataka,Bangalore' I am getting mutlipe result like all india records and karnataka records and bangalore records ,it is not like which record have all this value , I think in this case it is applying OR in between the conditions

When I am using servre side script (server.php) how can I get exactly result ,

For better understanding https://datatables.net/examples/basic_init/zero_configuration.html
in that link if you search with London Software Engineer all London and Software Engineer records only showing but when we using server side it not showing like that ,It is showing like all london records along with all Software Engineer records ,

How can I fix this issue plz help on it what I have to do .

Thanks
Sandeep

colin

This thread should help, it's asking the same thing.

Cheers,

Colin

SandeepMurala

@colin thanks for your valuble respones ,

I hope your are understood my problem ,I am new to this conecpt ,I am trying to learn more things here ,I went through your link ,but I did not get that ans. ,

Could you plz give some sample code or some path ,that is very usefull for me ,I spent lot of time on it but I failed to get the code ,

If you give that solution that is very helpfull and appriciated ..

Thanks
Sandeep

colin

It's all in that thread I posted, I can't add any more. Feel free to post what you've done, we can take a look,

Colin

SandeepMurala

@colin ,Sorry for late response , I have been searching regarding my problem but I did not get ans anythere ,Then I have added the code which you have shared on link ,
that code I have added in ssp.classs.php
here is my code kinldy check and respond where I did went wrong .

$file = $_SERVER['DOCUMENT_ROOT'].'/datatables/mysql.php';
if ( is_file( $file ) ) {
    include( $file );
}
class SSP {

    static function data_output ( $columns, $data )
    {
        $out = array();

        for ( $i=0, $ien=count($data) ; $i<$ien ; $i++ ) {
            $row = array();

            for ( $j=0, $jen=count($columns) ; $j<$jen ; $j++ ) {
                $column = $columns[$j];

                // Is there a formatter?
                if ( isset( $column['formatter'] ) ) {
                    $row[ $column['dt'] ] = $column['formatter']( $data[$i][ $column['db'] ], $data[$i] );
                }
                else {
                    $row[ $column['dt'] ] = $data[$i][ $columns[$j]['db'] ];
                }
            }

            $out[] = $row;
        }

        return $out;
    }

    static function limit ( $request, $columns )
    {
        $limit = '';

        if ( isset($request['start']) && $request['length'] != -1 ) {
            $limit = "LIMIT ".intval($request['start']).", ".intval($request['length']);
        }

        return $limit;
    }

    static function order ( $request, $columns )
    {
        $order = '';

        if ( isset($request['order']) && count($request['order']) ) {
            $orderBy = array();
            $dtColumns = self::pluck( $columns, 'dt' );

            for ( $i=0, $ien=count($request['order']) ; $i<$ien ; $i++ ) {
                // Convert the column index into the column data property
                $columnIdx = intval($request['order'][$i]['column']);
                $requestColumn = $request['columns'][$columnIdx];

                $columnIdx = array_search( $requestColumn['data'], $dtColumns );
                $column = $columns[ $columnIdx ];

                if ( $requestColumn['orderable'] == 'true' ) {
                    $dir = $request['order'][$i]['dir'] === 'asc' ?
                        'ASC' :
                        'DESC';

                    $orderBy[] = '`'.$column['db'].'` '.$dir;
                }
            }

            $order = 'ORDER BY '.implode(', ', $orderBy);
        }

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

        if ( isset($request['search']) && $request['search']['value'] != '' ) {
            $col_fts_nor = "fts_obverse";
            $col_fts_rev = "fts_reverse";
            $col_fts = "fts";
            $str = $request['search']['value'];
 
            //check which search method is been requested
            if (strpos($str, '|') === 0) {
                $withlike = true;
            } else {
                $withlike = false;
            }
            $str = preg_replace('/[^\p{L}\p{N}_]+/u', ' ', $str);
            //remove trailing and leading \s
            $str = preg_replace('/^\s|\s$/', '', $str);
 
            //if containt the like string
            $counter = 0;
            $str_db = "";
            $str_array = explode(" ", $str);
            //$message = "wrong answer";
            //echo "<script type='text/javascript'>alert('$message');</script>";
             
            if ($withlike) {
                //convert string to array
                //loop through the array
                foreach($str_array AS $for_each) {
                    //ignore first - its the '|' char
                    //the first search is different, we dont need an AND on the beginning
                    if ($counter == 0){
                        //create the select command "(COLUMN like '%SEARCHSTRING%')"
                        $str_db = "(" . $col_fts_nor . " like '%" . $for_each . "%') ";
                    }
                    elseif ($counter >= 1) {
                        //continue buildind the SQL query
                        $str_db = $str_db . "AND " . "(" . $col_fts_nor . " like '%" . $for_each . "%') ";
                    }
                    $counter++;
                }
            }
            else {                 
                for($i=0; $i<count($str_array); $i++) {
                    $str_nor = $str_array[$i];
                    $str_rev = strrev($str_nor); //create the reversed search string
                     
                    //first scan
                    if ($i == 0){
                        $str_db = "Match(" . $col_fts_nor . ", " . $col_fts_rev . ") Against (\"+(" . $str_nor . "* " . $str_rev .  "*)";
                    }
                    else{
                        $str_db = $str_db . " +(" . $str_nor . "* " . $str_rev .  "*)";
                    }
                    //last scan
                    if ($i+1 == count($str_array)) {
                        $str_db = $str_db . "\" in boolean mode)";
                    }
                }
            }
 
            $globalSearch[] = $str_db;
        }

        // 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 = 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($default !== false){
            if($where !== '')
                $where = $default . ' AND ' . $where;
            else 
                $where = $default; 
        }
        if ( $where !== '' ) {
            $where = 'WHERE '.$where;
        }

        return $where;
    }

    static function simple ( $request, $sql_details, $table, $primaryKey, $columns,$default = false )
    {
        $bindings = array();
        $db = self::sql_connect( $sql_details );

        // Build the SQL query string from the request
        $limit = self::limit( $request, $columns );
        $order = self::order( $request, $columns );
        $where = self::filter( $request, $columns, $bindings,$default );

        // Main query to actually get the data
        $data = self::sql_exec( $db, $bindings,
            "SELECT SQL_CALC_FOUND_ROWS `".implode("`, `", self::pluck($columns, 'db'))."`
             FROM `$table`
             $where
             $order
             $limit"
        );

        // Data set length after filtering
        $resFilterLength = self::sql_exec( $db,
            "SELECT FOUND_ROWS()"
        );
        $recordsFiltered = $resFilterLength[0][0];

        // Total data set length
        $resTotalLength = self::sql_exec( $db,
            "SELECT COUNT(`{$primaryKey}`)
             FROM   `$table`".($default !== false ? ' WHERE '.$default : '')
        );
        $recordsTotal = $resTotalLength[0][0];


        /*
         * Output
         */
        return array(
            "draw"            => intval( $request['draw'] ),
            "recordsTotal"    => intval( $recordsTotal ),
            "recordsFiltered" => intval( $recordsFiltered ),
            "data"            => self::data_output( $columns, $data )
        );
    }

    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 )
            );
        }
        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 sql_exec ( $db, $bindings, $sql=null )
    {
        // Argument shifting
        if ( $sql === null ) {
            $sql = $bindings;
        }

        $stmt = $db->prepare( $sql );
        //echo $sql;

        // Bind parameters
        if ( is_array( $bindings ) ) {
            for ( $i=0, $ien=count($bindings) ; $i<$ien ; $i++ ) {
                $binding = $bindings[$i];
                $stmt->bindValue( $binding['key'], $binding['val'], $binding['type'] );
            }
        }

        // Execute
        try {
            $stmt->execute();
        }
        catch (PDOException $e) {
            self::fatal( "An SQL error occurred: ".$e->getMessage() );
        }

        // Return all
        return $stmt->fetchAll();
    }
    static function fatal ( $msg )
    {
        echo json_encode( array( 
            "error" => $msg
        ) );

        exit(0);
    }
    static function bind ( &$a, $val, $type )
    {
        $key = ':binding_'.count( $a );

        $a[] = array(
            'key' => $key,
            'val' => $val,
            'type' => $type
        );

        return $key;
    }
    static function pluck ( $a, $prop )
    {
        $out = array();

        for ( $i=0, $len=count($a) ; $i<$len ; $i++ ) {
            $out[] = $a[$i][$prop];
        }

        return $out;
    }
}

I am gettng this error like

DataTables warning: table id=quess_table_server - An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'fts_obverse' in 'where clause'

Plz help on it and give me one solution ,that is soo appriciated .

I have been struggling from last few days but I failed to get the ANS.

Thanks
Sandeep

colin

It's a SQL error, reporting that "ftl_obverse" is an unknown column. Can you run that query by hand? If so and it works, can you post that command and the output here.

Colin

SandeepMurala

@colin ,I have tried that one which you have given but it was not working for me ,
So I am sharing my code for global search ,In that one I can search multiple values like Bangalore,Karnataka,India In this senario I am getting not only Bangalore,Karnataka,India it is coming india related stats and Karnataka all cities and bangalore city ,

But I need only Bangalore,Karnataka,India this record ,I think It should take AND condition ,

Here is my Code from ss.class.php and filter function :

Note : This serach based on (, comma ) when we do multiple column search

static function filter ( $request, $columns, &$bindings,$default = false )
    {
        $globalSearch = array();
        $columnSearch = array();
        $dtColumns = self::pluck( $columns, 'dt' );
    
        if ( isset($request['search']) && $request['search']['value'] != '' ) {
            $str = $request['search']['value'];             
            $parts = explode(",",$str);
            foreach ($parts as $part) {
                $part = trim($part);
                if (strlen($part) == 0) {
                    continue;
                }
                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, '%'.$part.'%', 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'];
            $parts = explode(",",$str);
                foreach ($parts as $part) {
                    $part = trim($part);
                    if (strlen($part) == 0) {
                        continue;
                    }
                if ( $requestColumn['searchable'] == 'true' ) { //&& $part != ''
                    $binding = self::bind( $bindings, '%'.$part.'%', PDO::PARAM_STR );
                    $globalSearch[] = "`".$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($default !== false){
                if($where !== ''){
                    //$where.= $default . ' OR ' . $where;
                    $where = $default . ' AND ' . $where;
                }
                else {
                    $where = $default; 
                }
        }       
        if ( $where !== '' ) {
            $where = 'WHERE '.$where;
        }
        
        return $where;
        
    }

Give me solution On it plz .

Thanks
Sandeep

allan

Hi Sandeep,

The SSP demo class doesn't do "smart" searching in the same way as DataTables client-side does. On the client-side it will split the words up by white-space and then do a search across the row for those words in any order. On the server-side however, the full string for the search term needs to be present in one of the columns.

This is the code for how that works.

If you wanted it to behave more like the client-side, you would need to add a split on the whitespace and then loop over the resulting array, adding suitable search conditions.

I did actually implement that for the Editor PHP libraries once, and the code is still there, just commented out and you could use that as a basis for modifying SSP if you wanted.

Allan

SandeepMurala

@colin and @allan thanks for you valuable response ,
I have done this my self , after split the code from fundamental ,I did it ,
I want to share that code here bez you saved me guys so many times ,This code working for me as I expected ,

static function filter ( $request, $columns, &$bindings,$default = false )
    {
        $globalSearch = array();
        $columnSearch = array();
        $dtColumns = self::pluck( $columns, 'dt' );
        
        if ( isset($request['search']) && $request['search']['value'] != '' ) {
            $str = $request['search']['value'];             
            $parts = explode(",",$str);
            foreach ($parts as $part) {
                $part = trim($part);
                if (strlen($part) == 0) {
                    continue;
                }
                $globalSearch[] = '(';   
                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, '%'.$part.'%', PDO::PARAM_STR );                  
                        $globalSearch[] = "`".$column['db']."` LIKE ".$binding;
                    }               
                }
                $globalSearch[] = ')';
            }
        }  
        // 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'];
            $parts = explode(",",$str);
                foreach ($parts as $part) {
                    $part = trim($part);
                    if (strlen($part) == 0) {
                        continue;
                    }
                    $globalSearch[] = '(';
                if ( $requestColumn['searchable'] == 'true' ) { //&& $part != ''
                    $binding = self::bind( $bindings, '%'.$part.'%', PDO::PARAM_STR );
                    $globalSearch[] = "`".$column['db']."` LIKE ".$binding;
                }
                $globalSearch[] = ')';
            }
        }
        
            //} 
        //}
        // Combine the filters into a single string
        $where = '';
        if ( count( $globalSearch ) ) {
                $where1 = implode(' OR ', $globalSearch);
                $where2 = str_replace(") OR (",") AND (",$where1);                  
                $where3 = str_replace("OR )"," )",$where2);
                $where = str_replace("( OR","( ",$where3);      
        }
        if ( count( $columnSearch ) ) {  
            $where = $where === '' ?
                implode(' AND ', $columnSearch) :
                $where .' AND '. implode(' AND ', $columnSearch);
        }
        if($default !== false){
                if($where !== ''){
                    //$where.= $default . ' OR ' . $where;
                    $where = $default . ' AND ' . $where;
                }
                else {
                    $where = $default; 
                }
        }       
        if ( $where !== '' ) {
            $where = 'WHERE '.$where;
        }
        
        return $where;
        
        
    }

Thanks
Sandeep

colin

Nice, thanks for reporting back,

Colin