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

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

SandeepMuralaSandeepMurala Posts: 30Questions: 8Answers: 0

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

Answers

  • SandeepMuralaSandeepMurala Posts: 30Questions: 8Answers: 0

    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

  • colincolin Posts: 12,453Questions: 0Answers: 2,110

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

    Cheers,

    Colin

  • SandeepMuralaSandeepMurala Posts: 30Questions: 8Answers: 0

    @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

  • colincolin Posts: 12,453Questions: 0Answers: 2,110

    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

  • SandeepMuralaSandeepMurala Posts: 30Questions: 8Answers: 0

    @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

  • colincolin Posts: 12,453Questions: 0Answers: 2,110

    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

  • SandeepMuralaSandeepMurala Posts: 30Questions: 8Answers: 0

    @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

  • allanallan Posts: 54,542Questions: 1Answers: 8,528 Site admin

    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

  • SandeepMuralaSandeepMurala Posts: 30Questions: 8Answers: 0

    @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

  • colincolin Posts: 12,453Questions: 0Answers: 2,110

    Nice, thanks for reporting back,

    Colin

Sign In or Register to comment.