Search field doesn't search in hidden column

paintball85

Hi there,

I have a dynamic generated table.
Search box does funny things.

It doen'st show all fields with search key in it and it wont search on a hidden column with searchable true.

http://www.fleischer.nl/GNL/returns/view/

Search Grundfos (in there 2 times) will only result 1 row.
When you enter a space after Grundfos no rows are shown.

Hidden Column is: Factuur
All rows have the value 2105000000

When column shown, all rows are shown.
When column hidden, no rows will show.

Does anyone have any idea what's going on?

            $('table.returns').DataTable({
                "language": {
                    "url": "http://cdn.datatables.net/plug-ins/1.10.12/i18n/Dutch.json"
                },
                "processing": true,
                "serverSide": true,
                "ajax": "<? echo MAP_URL; ?>returns/handle/fetch_data.php?cat=open_returns",
                "columnDefs": [
                {
                    "targets": -2,
                    "orderable": false
                },
                {
                    "targets": [-3,0],
                    "orderable": false,
                    "sClass": "text-center"
                },
                {
                    "targets": [8],
                    "visible": true,
                    "searchable": true
                }
                ],
                "aaSorting": [ [1,'desc'] ],
                "lengthMenu": [[10, 15, 25, 50], [10, 15, 25, 50]]
            });

elseif(!empty($_GET['cat']) && $_GET['cat'] == 'open_returns'):
    $table = 'returns';
    $primaryKey = 'id';
    $columns = array(
        array( 'db' => '`r`.`rma`',         'dt' => 0, 'formatter' => function( $d, $row ) 
        { 
            return ''; 
        }, 'field' => 'rma'),
        array( 'db' => '`r`.`rma`',         'dt' => 1, 'field' => 'rma'),
    array( 'db' => '`c`.`customer_id`',         'dt' => 2, 'formatter' => function( $d, $row ) 
        { 
            if(!empty($d))
            {
                $db = array ( 
                    'host'      => DATABASE_HST, 
                    'dbname'    => DATABASE_DB, 
                    'user'      => USER_DB, 
                    'pass'      => PASS_DB 
                ); 

                $db = new PDO('mysql:host='.$db['host'].';dbname='.$db['dbname'], $db['user'], $db['pass']); 
                $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
                $db->query("SET SESSION sql_mode = 'ANSI,ONLY_FULL_GROUP_BY'");
                
                $nameSql = "
                SELECT
                    company
                FROM
                    clients
                WHERE
                    customer = '".$row['customer_id']."'
                LIMIT 1
                "; 
                $nameStmt = $db->prepare($nameSql); 
                $nameStmt->execute();
                if($nameStmt->rowCount() > 0)
                {
                    while($nameRow = $nameStmt->fetch(PDO::FETCH_ASSOC))
                    {
                        return $nameRow['company'];
                    }
                } else
                {
                    return 'Naam niet gevonden.';
                }
            }
        }, 'field' => 'customer_id'),
    array( 'db' => '`c`.`name`',    'dt' => 3, 'field' => 'name'),
    array( 'db' => '`c`.`email`',   'dt' => 4, 'field' => 'email'),
    array( 'db' => '`r`.`created`', 'dt' => 5, 'field' => 'created' ),
        array( 'db' => '`r`.`rma`',         'dt' => 6, 'formatter' => function( $d, $row ) 
        { 
            if(!empty($d))
            {
                $db = array ( 
                    'host'      => DATABASE_HST, 
                    'dbname'    => DATABASE_DB, 
                    'user'      => USER_DB, 
                    'pass'      => PASS_DB 
                ); 

                $db = new PDO('mysql:host='.$db['host'].';dbname='.$db['dbname'], $db['user'], $db['pass']); 
                $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
                $db->query("SET SESSION sql_mode = 'ANSI,ONLY_FULL_GROUP_BY'");
                /*$sqlCon = "
                SELECT 
                    status
                FROM
                    return_pickups
                WHERE
                    rma_nr = '".$row['rma_nr']."'
                ";
                $stmtCon = $db->prepare($sqlCon); 
                $stmtCon->execute(); 
                
                while($rowCon = $stmtCon->fetch(PDO::FETCH_ASSOC)) 
                {
                    if($rowCon['status'] == "pending") {        $colorCon = 'warning'; }
                    if($rowCon['status'] == "exception") {  $colorCon = 'danger'; }
                    if($rowCon['status'] == "returned") {   $colorCon = 'success'; }
                }*/
                $colorCon = 'danger';
                $d = '12345678';
                return '
                    
                ';
            }
        }, 'field' => 'rma'),
    array( 'db' => '`r`.`id`', 'dt' => 7, 'formatter' => function( $d, $row ) 
        { 
            return '  '; 
        }, 'field' => 'rma'),
        array( 'db' => '`r`.`rma`', 'dt' => 8, 'formatter' => function( $d, $row ) 
        { 
            return '2105000000'; 
        }, 'field' => 'rma')
    );
    // SQL server connection information
    $sql_details = array(
            'user' => USER_DB,
            'pass' => PASS_DB,
            'db'   => DATABASE_DB,
            'host' => DATABASE_HST
    );
    require( '../../classes/ssp.class2.php' );
    $joinQuery  = "FROM `returns` AS `r` JOIN `contacts` AS `c` ON (`c`.`contact_id` = `r`.`contact_id`)";
    $extraWhere = "`r`.`status` = 'active'";
    echo json_encode(SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery, $extraWhere ));

paintball85

Thank you in advance!