Server-side column filtering solution using select input

cdiazaus · April 2015

I came up with a solution to this but couldn't fit all the code here.

I posted it on StackOverflow.

http://stackoverflow.com/questions/27299259/php-datatables-server-side-individual-column-filtering-only-gives-first-pages/29488533#29488533

cdiazaus · May 2015

Made some improvements. Took out column exposure and made the code more flexible.

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">

    <title>Whatever Page</title>
    <link rel="stylesheet" type="text/css" href="./css/jquery.dataTables.css">

    <script type="text/javascript" language="javascript" src="./js/jquery-1.11.1.min.js"></script>
    <script type="text/javascript" language="javascript" src="./js/jquery.dataTables.min.js"></script>  
    <script type="text/javascript" language="javascript" class="init">

var colId = new Array();
var colVal = new Array();
var selInitd = new Array();

$(document).ready(function() {
    function getSelectedList(coln){
        //get values for selection drop down
        var response = new Array();
        var returnArray = new Array();
        $.ajax({
            url: "filter.php",
            data: "selectedList="+coln,
            async: false,
            success: function(msg){
                response = msg.split(',');
                for(m = 0; m < response.length; m++){
                  //need to take care of special characters for passing these values back to the page filter 
                  response[m] = response[m].replace(/[^a-zA-Z0-9 ]/g, "");
                    
                  var val = response[m];
                  returnArray.push(val);
                }
            }
        })

        return returnArray;
    } 

  var columnNumber = $('#table thead th').length;

  var table = $('#table')
    .DataTable( {
        scrollX: true,
        dom: '<"top"fl>rt<"bottom"ipT><"clear">',
        serverSide: true,
        processing: true,
        ordering: true,
        order: [[ 9 ]],
        ajax: {
            url: "dt.php",
            data: function ( json ){
                json.ids = colId;
                json.values = colVal; 
            },
        },
        initComplete: function (settings, json) {
             var api = this.api();

            api.columns().indexes().flatten().each( function ( i ) {
                selInitd[i] = false;
                var column = api.column( i );
                var select = $('<select id=' + i + '><option value=""></option></select>')
                    .appendTo( $(column.footer()).empty() )
                    .on( 'change', function () {
                        //cycle through every column and get any selected filters
                        for (k = 0; k < columnNumber; k++){
                            var value = document.getElementById(k).value;
                            //add any found filter arrays that are used to display pages
                            if (value != "" ){
                                colId.push(k);
                                colVal.push(value);
                            }
                        }

                        api.ajax.reload( null, true );

                        colId = [];
                        colVal = [];

                    } )
                    .on( 'focus', function () {
                        //only want to perform this once per click/page refresh or else you get duplicate values
                        if(!selInitd[i]){
                            var selectedL = getSelectedList(i).slice();
                    
                            for(d = 0; d < selectedL.length; d++) {
                                select.append( '<option value="' + selectedL[d] + '">' + selectedL[d] + '</option>' )
                            }
                        
                            selInitd[i] = true;
                        }
                    } );
            } );
        }
    } );

    $(".dataTables_filter input")
        .on("input.dt", function(e) {
            //reset select inputs
            for(h = 0; h < columnNumber; h++){
                var value = document.getElementById(h);
                value.selectedIndex = 0;
            }

            return;
        });

    $(".dataTables_scrollHeadInner th.sorting")
        .on("click.dt", function(e) {
            //reset select inputs
            for(h = 0; h < columnNumber; h++){
                var value = document.getElementById(h);
                value.selectedIndex = 0;
            }

            return;
        });

    $(".dataTables_length select")
        .on("click.dt", function(e) {
            //reset select inputs
            for(h = 0; h < columnNumber; h++){
                var value = document.getElementById(h);
                value.selectedIndex = 0;
            }

            return;
        });

    table.on("page.dt", function(e) {
            //reset select inputs
            for(h = 0; h < columnNumber; h++){
                var value = document.getElementById(h);
                value.selectedIndex = 0;
            }

            return;
        });

} );

    </script>
</head>

</body>
<div class="container">
    <table id="table" class="display" cellspacing="0" width="100%">
        <thead>
            <tr>
            <th>colA</th>
            <th>colB</th>
            <th>colC</th>
            <th>colD</th>
            <th>colE</th>
            <th>colF</th>
            <th>colG</th>
            <th>colH</th>
            <th>colI</th>
            <th>colJ</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
            <th>colA</th>
            <th>colB</th>
            <th>colC</th>
            <th>colD</th>
            <th>colE</th>
            <th>colF</th>
            <th>colG</th>
            <th>colH</th>
            <th>colI</th>
            <th>colJ</th>
            </tr>
        </tfoot>
    </table>
</div>
</body>
</html>

cdiazaus · May 2015

dt.php

<?php

/*
 * DataTables example server-side processing script.
 *
 * Please note that this script is intentionally extremely simply to show how
 * server-side processing can be implemented, and probably shouldn't be used as
 * the basis for a large complex system. It is suitable for simple use cases as
 * for learning.
 *
 * See http://datatables.net/usage/server-side for full details on the server-
 * side processing requirements of DataTables.
 *
 * @license MIT - http://datatables.net/license_mit
 */
 
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * Easy set variables
 */
 
// DB table to use
$table = 'table;
 
// Table's primary key
$primaryKey = 'colA';
 
// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
    array( 'db' => 'colA',  'dt' => 0 ),
    array( 'db' => 'colB',  'dt' => 1 ),
    array( 'db' => 'colC',  'dt' => 2 ),
    array( 'db' => 'colD',  'dt' => 3 ),
    array( 'db' => 'colE',  'dt' => 4 ),
    array( 'db' => 'colF',  'dt' => 5 ),
    array( 'db' => 'colG',  'dt' => 6 ),
    array( 'db' => 'colH',  'dt' => 7 ),
    array( 'db' => 'colI',  'dt' => 8 ),
    array( 'db' => 'colJ',  'dt' => 9 )
);
 
// SQL server connection information
$sql_details = array(
    'user' => 'user',
    'pass' => 'password',
    'db'   => 'database',
    'host' => 'server'
);

//pars arrays from html and form where clause for dt
$whereAllClause = '';

$valuesArr = array();
$valuesArr = $_GET['values'];

foreach ($_GET['ids'] as $idx=>$selectedOption){
    if (strncmp($whereAllClause,'',1) > 0){
        $whereAllClause = $whereAllClause." AND ".$columns[$selectedOption]['db']." = '".$valuesArr[$idx]."'";
    }else{
        $whereAllClause = $columns[$selectedOption]['db']." = '".$valuesArr[$idx]."'";
    }
}
    

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * If you just want to use the basic configuration for DataTables with PHP
 * server-side, there is no need to edit below this line.
 */

require( './ssp.class.php' );
 
echo json_encode(
    SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, null, $whereAllClause )
);

cdiazaus · May 2015

filter.php

<?php
$column = $_GET['selectedList'];

$table = array(
    array( 'db' => 'colA',  'dt' => 0 ),
    array( 'db' => 'colB',  'dt' => 1 ),
    array( 'db' => 'colC',  'dt' => 2 ),
    array( 'db' => 'colD',  'dt' => 3 ),
    array( 'db' => 'colE',  'dt' => 4 ),
    array( 'db' => 'colF',  'dt' => 5 ),
    array( 'db' => 'colG',  'dt' => 6 ),
    array( 'db' => 'colH',  'dt' => 7 ),
    array( 'db' => 'colI',  'dt' => 8 ),
    array( 'db' => 'colJ',  'dt' => 9 )
);

//conection: 
$link = mysqli_connect("server","id","password","datatabse") or die("Error " . mysqli_error($link)); 

//consultation: 

$query = "SELECT DISTINCT ".$table[$column]['db']." FROM table ORDER BY ".$table[$column]['db']." ASC" or die("Error in the consult.." . mysqli_error($link)); 

//execute the query. 

$result = $link->query($query); 

//display information: 

$rows = array();
$rIdx = 0;

while($row = mysqli_fetch_array($result)) { 
  $rows[$rIdx] = $row[0]; 
  $rIdx++;
} 

if($rows){
  echo json_encode($rows);
}

Server-side column filtering solution using select input

Server-side column filtering solution using select input

Replies

Howdy, Stranger!

Categories

DataTables

Server-side column filtering solution using select input

Server-side column filtering solution using select input

Replies

Howdy, Stranger!

Quick Links

Categories

DataTables