Server-Side Processing PHP MSSQL - Individual Column Filtering

Caldaga · June 2017

I am having a hard time figuring out how to enable individual column searching using server-side processing. My server-script is one I believe I found somewhere on this site, although I can't seem to find it again.

Server-Side PHP:

<?php
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/

/* Array of database columns which should be read and sent back to DataTables. Use a space where
* you want to insert a non-database field (for example a counter or static image)
*/

// add your columns here!!!
    $aColumns = $_POST['selcolumns'];
    $aColumns = explode(",", $aColumns);
    array_pop($aColumns);

    $server = "";
    $database = array("Database" => "");
    $conn = sqlsrv_connect($server, $database); 
    if ($conn === false) die("<pre>".print_r(sqlsrv_errors(), true));



/* Indexed column (used for fast and accurate table cardinality) */
    $sIndexColumn = "GUID";

/* DB table to use */
    $sTable = $_POST['table'];

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

/*
* Local functions
*/
    function fatal_error ( $sErrorMessage = '' ) {
        header( $_SERVER['SERVER_PROTOCOL'] .' 500 Internal Server Error' );
        die( $sErrorMessage );
    }

/* Ordering */
    $sOrder = "";
    if ( isset( $_POST['order'] ) ) {
        $sOrder = "ORDER BY ";
        if ( $_POST['columns'][0]['orderable'] == "true" ) {
            $sOrder .= "".$aColumns[ intval( $_POST['order'][0]['column'] ) ]." ".
            ($_POST['order'][0]['dir']==='asc' ? 'asc' : 'desc');
        }
    }

/* escape function */
    function mssql_escape($data) {
        if(is_numeric($data))
        return $data;
        $unpacked = unpack('H*hex', $data);
        return '0x' . $unpacked['hex'];
    }

/* Filtering */
    $sWhere = "";
    if ( isset($_POST['search']['value']) && $_POST['search']['value'] != "" ) {
        $sWhere = "WHERE (";
        for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
            $sWhere .= $aColumns[$i]." LIKE '%".addslashes( $_POST['search']['value'] )."%' OR ";
        }
        $sWhere = substr_replace( $sWhere, "", -3 );
        $sWhere .= ')';
    }

/* Individual column filtering */
    for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
        if ( isset($_POST['columns'][$i]) && $_POST['columns'][$i]['searchable'] == "true" && $_POST['columns'][$i]['search']['value'] != '' ) {
            if ( $sWhere == "" ) {
                $sWhere = "WHERE ";
            }
            else {
                $sWhere .= " AND ";
            }
            $sWhere .= $aColumns[$i]." LIKE '%".addslashes($_POST['columns'][$i]['search']['value'])."%' ";
        }
    }

/* Add the custom Date/Time filter */

    if ( $sWhere == "" ) {
        $sWhere = "WHERE (TimeOccurred >= "."'".$_POST['datestart']."'"." AND TimeOccurred <= "."'".$_POST['dateend']."')";
    }
    else {
        $sWhere .= " AND (TimeOccurred >= "."'".$_POST['datestart']."'"." AND TimeOccurred <= "."'".$_POST['dateend']."')";
    }

/* Paging */
    $top = (isset($_POST['start']))?((int)$_POST['start']):0 ;   
    $limit = (isset($_POST['length']))?((int)$_POST['length'] ):5;
    $sQuery = "SELECT TOP $limit ".implode(', ', $aColumns)." FROM $sTable $sWhere ".(($sWhere=="")?" WHERE ":" AND ")." $sIndexColumn NOT IN ( SELECT TOP $top $sIndexColumn FROM $sTable $sOrder ) $sOrder";
    $rResult = sqlsrv_query($conn, $sQuery);
    if($rResult === false){
        die(sqlsrv_errors(SQLSRV_ERR_ERRORS));
    }

/* Data set length after filtering */
    $sQueryCnt = "SELECT * FROM $sTable $sWhere";
    $rResultCnt = sqlsrv_query($conn, $sQueryCnt, array(), array("Scrollable" => SQLSRV_CURSOR_KEYSET));
    $iFilteredTotal = sqlsrv_num_rows( $rResultCnt );

/* Total data set length */
    $sQuery = "SELECT COUNT(GUID) FROM $sTable";
    $rResultTotal = sqlsrv_query($conn, $sQuery, array(), array("Scrollable" => SQLSRV_CURSOR_KEYSET));
    $aResultTotal = sqlsrv_fetch_array($rResultTotal, SQLSRV_FETCH_NUMERIC);
    $iTotal = $aResultTotal[0];

/* Output */
    $output = array(
        "draw" => intval($_POST['draw']),
        "recordsTotal" => $iTotal,
        "recordsFiltered" => $iFilteredTotal,
        "data" => array()
    );

    while ( $aRow = sqlsrv_fetch_array( $rResult, SQLSRV_FETCH_ASSOC) ) {
        $row = array();
        for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
            $row[$aColumns[$i]] = $aRow[ $aColumns[$i] ];
        }
        $output['data'][] = $row;
    }

    echo json_encode( $output );

?>

It has a section for "Individual column filtering", so I think it is already prepared to do the search properly I just need to figure out how to add the fields to the table and send the values to the scripts. I was thinking it might be as simple as adding a letter to dom under my datatables initialization but I'm not sure.

This is my Javascript just in case that helps:

    <script type="text/javascript">
        $(document).ready(function() {
            $('#myTable').DataTable( {
                dom: 'Bfrti',
                select: true,
                buttons: [ 'colvis' ],
                deferRender: true,
                select: true,
                colReorder: true,
                scroller: { loadingIndicator: true },
                scrollX: true,
                scrollY: 700,
                processing: true,
                serverSide: true,
                ajax: {
                    url: '../php/queryresults.php',
                    type: "POST",
                    data: 
                    { 
                        <?php $dateArray = explode(" ", $_POST['datefilter']); $startDate = ($dateArray[0]." ".$dateArray[1]); $endDate = ($dateArray[3]." ".$dateArray[4]); ?>
                        table: '<?php echo $_POST['table']; ?>',
                        datestart: '<?php echo $startDate ?>',
                        dateend: '<?php echo $endDate ?>',
                        selcolumns: '<?php foreach ($_POST['selectedcolumns'] as $col) { $selColumns .= $col.","; } echo $selColumns; ?>'
                    },
                },
                <?php
                echo "columns: [";
                foreach ($_POST['selectedcolumns'] as $col) {
                    if ($col == "TimeOccurred") {
                        $col = "TimeOccurred.date.";
                    }
                    echo '{ "data": "'.$col.'" },';
                    unset($col);
                }
                echo "]";
                ?>
            } );
        } );
    </script>

Any guidance would be greatly appreciated.

tangerine · June 2017

There is nothing in your javascript to enable individual column filtering.
Your first port of call should be the relevant documentation.
https://datatables.net/examples/api/multi_filter.html

Caldaga · June 2017

Thank you, I knew I had seen that somewhere weeks ago but had decided to come back to it after I had other things working, then I couldn't find it again.

Caldaga · June 2017

I got it to work as expected from there for the most part. The only issue I have now is filtering on my date/time column. It is in this format: 2017-05-31 16:22:00 and if I type 2017- as soon as I put in the - I get no matches found. Every entry contains 2017-.

allan · June 2017

Since you are using server-side processing (serverSide) this is an issue with the filtering being done by the ../php/queryresults.php script and its interaction with the database.

I'd suggest echoing out the SQL statement it is building so you can debug that.

Allan

Caldaga · June 2017

Thanks for the reply Allan, turns out that isn't the best way to filter on a date/time column. Is there a simple way to make the individual column filtering ignore a specific column (like not even put the field there for it? I believe this is the code that creates the fields:

        $('#myTable tfoot th').each( function () {
            var title = $(this).text();
            $(this).html( '<input type="text" placeholder="Search '+title+'" />' );
        } );

allan · June 2017

Modify your selector (#myTable tfoot th) so that it only selects the columns you want. At the moment it is selecting all th elements in the table footer. You could add a class of searchable to the columns you want to be searchable for example.

Allan

Caldaga · June 2017

This is what I wound up doing on the server side php to fix my issue with filtering on the date/time.

for ( $i=0 ; $i<count($aColumns) ; $i++ ) {
        if ( isset($_POST['columns'][$i]) && $_POST['columns'][$i]['searchable'] == "true" && $_POST['columns'][$i]['search']['value'] != '' ) {
            if ( $_POST['columns'][$i]['data'] != 'TimeOccurred.date.' ) {
                if ( $sWhere == "" ) {
                    $sWhere = "WHERE ";
                }
                else {
                    $sWhere .= " AND ";
                }
                $sWhere .= $aColumns[$i]." LIKE '%".addslashes($_POST['columns'][$i]['search']['value'])."%' ";
            }
            else {
            
                $dateArrayColumn = explode(' ', $_POST['columns'][$i]['search']['value']);
                $startDateColumn = ($dateArrayColumn[0]." ".$dateArrayColumn[1]);
                $endDateColumn = ($dateArrayColumn[3]." ".$dateArrayColumn[4]);
                
                if ( $sWhere == "" ) {
                    $sWhere = "WHERE (TimeOccurred >= "."'".$startDateColumn."'"." AND TimeOccurred <= "."'".$endDateColumn."')";
                }
                else {
                    $sWhere .= " AND (TimeOccurred >= "."'".$startDateColumn."'"." AND TimeOccurred <= "."'".$endDateColumn."')";
                }
            
            }
        }
    }

In the 2nd if statement I check to see if it is the date/time column and if not continue with regular filtering. Then in the else statement I filter on the date/time column in a better way for SQL than trying to do a 'like'. Hopefully this helps someone else.

Caldaga · June 2017

Also realized I never posted my Datatables initialization after you guys helped me figure out the column filtering. Here it is:

    <script type="text/javascript">
        $(document).ready(function() {
            $('#myTable tfoot th').each( function () {
                var title = $(this).text();
                $(this).html( '<input type="text" placeholder="Search '+title+'" />' );
            } );
            
            var table = $('#myTable').DataTable( {
                dom: 'Bfrti',
                buttons: [ 'colvis', 'csv', 'pdf', 'print' ],
                deferRender: true,
                select: true,
                colReorder: true,
                scroller: { loadingIndicator: true },
                scrollX: true,
                scrollY: 650,
                processing: true,
                serverSide: true,
                ajax: {
                    url: '../php/queryresults.php',
                    type: "POST",
                    data: 
                    { 
                        <?php $dateArray = explode(" ", $_POST['datefilter']); $startDate = ($dateArray[0]." ".$dateArray[1]); $endDate = ($dateArray[3]." ".$dateArray[4]); ?>
                        table: '<?php echo $_POST['table']; ?>',
                        datestart: '<?php echo $startDate ?>',
                        dateend: '<?php echo $endDate ?>',
                        selcolumns: '<?php foreach ($_POST['selectedcolumns'] as $col) { $selColumns .= $col.","; } echo $selColumns; ?>',
                        cannedfilter: '<?php echo $_POST['title']; ?>'
                    },
                },
                <?php
                echo "columns: [";
                foreach ($_POST['selectedcolumns'] as $col) {
                    if ($col == "TimeOccurred") {
                        $col = "TimeOccurred.date.";
                    }
                    echo '{ "data": "'.$col.'" },';
                    unset($col);
                }
                echo "]";
                ?>
            } );
            table.columns().every( function () {
                var that = this;
         
                $( 'input', this.footer() ).on( 'keyup change', function () {
                    if ( that.search() !== this.value ) {
                        that
                            .search( this.value )
                            .draw();
                    }
                } );
            } );
        } );
    </script>

jalape · September 2018

Hello,
I am trying to add a functionality to a datatable, which consists of a search engine by independent column.
From the example:
https://datatables.net/examples/api/multi_filter.html
I have added the corresponding imput, but the problem is in the function
$ ('input', this. header ()) .on ('keyup change', function () {
if (that.search ()! == this.value) {
that
.search (this.value)
.draw ();
}
});

When filling any of the text boxes to do a search, the error is generated.
With the web development tool Firefox: network, I found that the error is that, when you type a character in the text box, add that character to the name of the column and logically can not find the column.
Ex: I write tx in the column: 'tb_asignatura.asignatura
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE [42S22]: Column not found: 1054 Unknown column' tb_asignatura.asignaturatx
I would greatly appreciate any suggestions to fix it.
a greeting

Server-Side Processing PHP MSSQL - Individual Column Filtering

Server-Side Processing PHP MSSQL - Individual Column Filtering

Answers

Howdy, Stranger!

Categories

DataTables

Server-Side Processing PHP MSSQL - Individual Column Filtering

Server-Side Processing PHP MSSQL - Individual Column Filtering

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables