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
<?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));

<?php > ``` ?>

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

```php
<?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';
}
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;
}

}

<?php > ``` ?>

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 );