My individual column searching (text inputs) are not searching on their columns, but only on the ID

mmcnair80

I have the table rendering correctly and am using fixed header and footer, and server side processing. I've added the Individual column searching (text inputs) based on what I found here. However, no matter which filter box I type into all of them search only on the ID (the first column) and not on the column that they are under.

Here's how I initialize the DatatTable:

$(document).ready(function () {
    
        // Setup - add a text input to each footer cell
        $('#DataTable tfoot th').each(function () {
            var title = $(this).text();
            $(this).html('<input type="text" placeholder="Search ' + title + '" />');
        });

        var table = $('#DataTable').DataTable({
                "lengthMenu" : [[25, 50, 75, 100, 150], [25, 50, 75, 100, 150]],
                "dom" : '<"top"Bilp<"clear">>rt<"bottom"ip<"clear">>',
                "buttons" : [{
                        extend : 'collection',
                        text : 'Selection',
                        buttons : ['selectAll', 'selectNone']
                    }, {
                        extend : 'collection',
                        text : 'Export',
                        buttons : ['excel', 'csv', 'pdf']
                    }
                ],
                "fixedHeader" : {
                    header : true,
                    footer : true
                },
                "select" : true,
                "processing" : true,
                "serverSide" : true,
                "ajax" : {
                    "url" : "./ServerSide.php",
                    "type": "POST"
                },
        initComplete: function() {
          var api = this.api();

          // Apply the search
          api.columns().every(function() {
            var that = this;

            $('input', this.footer()).on('keyup change', function() {
              if (that.search() !== this.value) {
                that
                  .search(this.value)
                  .draw();
              }
            });
          });
        }
      });
    });

Am I doing something wrong with this?

tangerine

The example you used does not have server-side processing.
As you are using serverSide processing, your searching (and sorting) should be handled by your own server-side script. With serverSide enabled, DataTables will just present what it's given.

mmcnair80

@tangerine Right, the example doesn't use Server-side processing, but I was only using that example for the filtering.
Here is my ServerSide.php file:

<?php
include 'Helper/PageName.php';
include 'DBConn.php'; //echo "<br>Testing";

$headings = array();
$hsql = "select Headings from TableHeadings where TableName = '$TableName' order by Id";

$getHeadings = $conn->query($hsql);
$rHeadings = $getHeadings->fetchALL(PDO::FETCH_ASSOC);
$CountHeadings = count($rHeadings); 
$tsqlHeadings = '';
$ColumnHeader = array();
for ($row = 0; $row < $CountHeadings; $row++)
{
    if($rHeadings[$row]["Headings"] <> "Edit")
    {
        $headings[$row] = $rHeadings[$row]["Headings"];
    }
}

foreach($headings as $index => $columnName)
{
    $ColumnHeader[] = array('db'=>$columnName,'dt'=>$index);
}

$table = $SQLTableName;

$primaryKey = 'id';

$request = array();
$_POST['PageName'] = $Page;
$request = $_POST;
require('FilterSort.class.php');

echo json_encode(FilterSort::complex($request,$sqlConnect,$table,$primaryKey,$ColumnHeader));
?>

And here is my FilterSort.class.php (a modified version of the ssp.class.php file):

<?php
class FilterSort
{
    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];
                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 db ($conn)
    {
        if(is_array($conn))
        {
            return self::sql_connect($conn);
        }
        return $conn;
    }

    static function limit ($request,$columns)
    {
        $limit = '';
        if(isset($request['start']) && $request['length'] != -1)
        {
            $limit = " OFFSET " . intval($request['start']) . " ROWS FETCH NEXT " . intval($request['length']) . " ROWS ONLY ";
        }
        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 )
    {
        if($request['draw'] > 1){
            print_r($bindings); echo " bindings<br>";
        }
       
        $globalSearch = array();
        $columnSearch = array();
        $dtColumns = self::pluck( $columns, 'dt' );
        if (isset($request['search']) && $request['search']['value'] != '')
        {
            $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')
                {
                    $binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
                    $globalSearch[] = "[" .$column['db']."] LIKE ".$binding;
                }
            }
        }

        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 ];
                $str = $requestColumn['search']['value'];
                if ( $requestColumn['searchable'] == 'true' &&
                 $str != '' )
                 {
                    $binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
                    $columnSearch[] = "[" .$column['db']."] LIKE ".$binding;
                }
            }
        }
        if($request['draw'] > 1)
        {
            print_r($ien); echo " ien<br>";
        }

        $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;
    }

    static function complex ( $request, $conn, $table, $primaryKey, $columns, $whereResult=NULL, $whereAll=NULL )
    {
        $bindings = array();
        $db = self::db( $conn );
        $localWhereResult = array();
        $localWhereAll = array();
        $whereAllSql = '';
        
        $limit = self::limit( $request, $columns );
        $order = self::order( $request, $columns );
        $where = self::filter( $request, $columns, $bindings );
        
        $whereResult = self::_flatten( $whereResult );
        $whereAll = self::_flatten( $whereAll );

        if ( $whereResult )
        {
            $where = $where ? $where .' AND '.$whereResult : ' WHERE '.$whereResult;
        }

        if ( $whereAll )
        {
            $where = $where ? $where .' AND '.$whereAll : ' WHERE '.$whereAll;
            $whereAllSql = ' WHERE '.$whereAll;
        }

        $data = self::sql_exec( $db, $bindings,"SELECT ".implode(", ", self::pluck($columns, 'db'))." FROM $table$where$order$limit");

        $resFilterLength = self::sql_exec( $db, $bindings,"SELECT COUNT({$primaryKey}) FROM $table $where" );
        $recordsFiltered = $resFilterLength[0][0];

        $resTotalLength = self::sql_exec( $db, ""/*$bindings*/, "SELECT COUNT({$primaryKey}) FROM $table ".$whereAllSql);
        $recordsTotal = $resTotalLength[0][0];

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

    static function sql_connect ( $sql_details )
    {
        try {
            $servername     = $sql_details['servername'];
            $username       = $sql_details['username'];
            $password       = $sql_details['password'];
            $dbname         = $sql_details['dbname'];
            $db             = @new PDO("sqlsrv:server=$servername;database=$dbname", $username,$password);
            $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $db->setAttribute(PDO::SQLSRV_ATTR_QUERY_TIMEOUT, 10);
        }
        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 )
    {
        if ( $sql === null ) 
        {
            $sql = $bindings;
            $Newsql = $sql;
        }

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

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

        try
        {
            $stmt->execute();
        }
        catch (PDOException $e)
        {
            self::fatal( "A SQL error has occurred: ".$e->getMessage() .  "   " . var_dump($sql) . "    " . var_dump($bindings) . "    " . var_dump($stmt));
        }

        return $stmt->fetchAll( PDO::FETCH_BOTH );
    }
    
    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;
    }

    static function _flatten ($a, $join = ' AND ')
    {
        if (!$a)
        {
            return '';
        }
        else if ($a && is_array($a))
        {
            return implode($join, $a);
        }
        return $a;
    }
}
?>

mmcnair80

I have found the error.

All of my column names are surrounded by []. So I found that I had to add that to the array_search in the filter function like this:

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

updated to:

$columnIdx = array_search( "[" .$requestColumn['data']. "]", $dtColumns );