Server-Side Processing PHP MSSQL - Individual Column Filtering

Caldaga

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

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

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

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

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

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

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

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

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

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