PHP Server side paging and filtering and sorting

1 posts, 0 answers
  1. DavidOBrien
    DavidOBrien avatar
    14 posts
    Member since:
    Sep 2012

    Posted 22 Aug 2014 Link to this post

    THIS IS NOT A QUESTION

    I just wanted to give back and maybe help someone else before they wasted as much time as I have :)

    Attached is my general all purpose read and update a grid ajax page written in php

    it'll parse any filter thrown at it  ( i think ) do server side paging ( MSSQL ) and handle Sorting on the backend

    If you have suggestions or better ways PLEASE let me know...

    <?php
    header("Cache-Control: No-cache");
    // THIS IS MY SAMPLE AJAX RESPONSE PAGE FOR ANY GRID I CREATE
     
    //Database Connection
    $serverName = "HOSTNAME";
    $connectionInfo = array( "Database"=>"DBNAME", "UID"=>"DBUSER", "PWD"=>"DSPASSWORD", 'ReturnDatesAsStrings'=>true );
    $DEVconn = sqlsrv_connect( $serverName, $connectionInfo );
    if( $DEVconn === false ) {
         echo "Connection could not be established.<br />";
         die( print_r( sqlsrv_errors(), true));
    }
     
    // Define the Default Sort Order if nothing is sent from the grid
    if ( !isset( $_REQUEST['sort'][0]['field'] ) ) { $_REQUEST['sort'][0]['field'] = "EmployeeID"; }
    if ( !isset( $_REQUEST['sort'][0]['dir']   ) ) { $_REQUEST['sort'][0]['dir'] = "asc"; }
     
    /*
     
    Parse the kendo passed filter info and make a sql server where clause out of it
    Example One: READING
     
    read transport config in the grid ...
        read: {
            url: "ajaxdata/grid_governance.php",
            dataType: "json",
            type: "POST",
            data: function() {
                return {
                    action: "ReadGrid"
                }
            }
        },
     
    Using that the grid sends this to this page...
     
        action:ReadGrid
        take:50
        skip:0
        page:1
        pageSize:50
        filter[logic]:and
        filter[filters][0][field]:UserDeviceCount
        filter[filters][0][operator]:eq
        filter[filters][0][value]:1
        filter[filters][1][logic]:or
        filter[filters][1][filters][0][field]:Office
        filter[filters][1][filters][0][operator]:gt
        filter[filters][1][filters][0][value]:90
        filter[filters][1][filters][1][field]:Remote
        filter[filters][1][filters][1][operator]:gt
        filter[filters][1][filters][1][value]:90
        filter[filters][1][filters][2][field]:Unknown
        filter[filters][1][filters][2][operator]:gt
        filter[filters][1][filters][2][value]:90
     
    And this page generates this SQL ...
     
        DECLARE @Page INT = 0;
        DECLARE @PageSize INT = 50;
        WITH    UNPAGED
                  AS (
                       SELECT
                            *
                          , COUNT(*) OVER ( ) AS Counter
                          , ROW_NUMBER() OVER ( ORDER BY EmployeeID ASC ) AS RowNumber
                        FROM
                            TABLENAME
                        WHERE                   
                            UserDeviceCount = '1'
                            AND ( Office > 90 OR Remote > 90 OR Unknown > 90 )
                     )
             SELECT * FROM [UNPAGED]
                WHERE [RowNumber] BETWEEN ( @Page * @PageSize + 1 ) AND ( @Page * @PageSize + @PageSize )
     
    And sends this JSON back to the grid
     
    {"data":
        [
            { "HostID":1573336,"UserID":1268510,"EmployeeID":20264,"Counter":13763,"RowNumber":"1"},
            { "HostID":5646467,"UserID":1268510,"EmployeeID":20264,"Counter":13763,"RowNumber":"2"},
            { "HostID":1237364,"UserID":1268510,"EmployeeID":20264,"Counter":13763,"RowNumber":"3"},
        ],
        "total": 13763 ,
        "sql": "DECLARE @Page INT = 0;<br /> DECLARE @PageSize INT = 50;<br /> WITH UNPAGED AS ( SELECT * <br /> ,COUNT(*) OVER() AS Counter <br /> ,ROW_NUMBER() OVER ( ORDER BY EmployeeID asc ) AS RowNumber <br /> FROM [LCMM].[dbo].[v_HostGovernanceMainGrid] <br /> WHERE UserDeviceCount = '1' and ( Office > 90 or Remote > 90 or Unknown > 90 ) <br /> ) SELECT * FROM [UNPAGED] WHERE [RowNumber] BETWEEN ( @Page * @PageSize + 1 ) AND ( @Page * @PageSize + @PageSize )"
    }
     
    The Default Filter Below Is To Return Everything
    */
    $where = " 1 = 1 ";
     
    if ( isset( $_REQUEST['filter'] )) $where = parseFilters( $_REQUEST['filter'] );
     
    // Initial count of the returned rows
    $total = 0;
     
    // What are we doing?
    switch ( $_REQUEST['action'] ) {
        case 'ReadGrid':       
            $unpaged = "SELECT
                     *
                    ,COUNT(*) OVER() AS Counter
                    ,ROW_NUMBER() OVER ( ORDER BY " . $_REQUEST['sort'][0]['field'] . " " . $_REQUEST['sort'][0]['dir'] . " ) AS RowNumber
                FROM TABLENAME WHERE $where
            ";   
            $sql = "DECLARE @Page INT = " . ( $_REQUEST['page'] - 1 ) . ";
                DECLARE @PageSize INT = " . $_REQUEST['pageSize'] . ";
                WITH UNPAGED AS ( $unpaged )
                SELECT * FROM [UNPAGED]
                    WHERE [RowNumber] BETWEEN ( @Page * @PageSize + 1 ) AND ( @Page * @PageSize + @PageSize ) ";
            $string=[];
            $results = sqlsrv_query( $DEVconn $sql ) or die( print_r( sqlsrv_errors(), true));
            $count = 0;
            while ( $row = sqlsrv_fetch_array( $results , SQLSRV_FETCH_ASSOC)) {
                // Only do this once
                if ( $count == 0 ) $total = $row['Counter'];
                $string[] = json_encode($row);
                $count++;
            }       
            header("Content-type: application/json");
            /*
             We also return the query itself in the response to aid in debugging...
             You just need an additional div id'd as "sql" on the calling page and add this to your datasource
     
            schema: {
                data: function(response) {
                    // Populated the div with the returned sql query
                    $("#sql").html( response.sql );
                    return response.data;
                },
                total: "total", 
     
            */
            echo "{\"data\": [" . join( $string , "," ) . "] ,\"total\": " . $total . " , \"sql\": \"" . oneline( $sql ) . "\"}";
            break;
        case 'UpdateGrid':        
            $return = 'true';
            foreach ( $_REQUEST['models'] as $record ) {
                $sql = "EXEC [dbo].[sp_StoredProcedure]
                            @Hostid   =" . $record['HostID'] . ",
                            @UserID   =" . $record['UserID'] . ",
                            @GovHardwareActionID         ='" . $record['GovHardwareActionID'] . "',
                            @GovHardwareDecisionID       ='" . $record['GovHardwareDecisionID']."',
                            @GovHardwareRecommendationID ='" . $record['GovHardwareRecommendationID'] . "',
                            @UserHWRecommendationID      ='" . $record['UserHWRecommendationID'] . "',
                            @ManagerHWRecommendedID      ='" . $record['ManagerHWRecommendedID'] . "',
                            @FinalHWRecommendationID     ='" . $record['FinalHWRecommendationID'] . "'
                        \n";  
                sqlsrv_query( $DEVconn $sql );
            }
            echo $return;
            break;
        default:
            break;
    }       
     
    function parseFilters( $filters , $count = 0 ) { 
        $where = "";   
        $intcount = 0;
        $noend= false;
        $nobegin = false;
        // Do we actually have filters or noi ?
        if ( isset( $filters['filters'] ) ) {       
            $itemcount = count( $filters['filters'] );
            if ( $itemcount == 0 ) {
                $noend= true;
                $nobegin = true;
            } elseif ( $itemcount == 1 ) {
                $noend= true;
                $nobegin = true;          
            } elseif ( $itemcount > 1 ) {
                $noend= false;
                $nobegin = false;          
            }
            foreach ( $filters['filters'] as $key => $filter ) {
                if ( isset($filter['field'])) {
                    switch ( $filter['operator'] ) {
                        case 'startswith':
                            $compare = " LIKE ";
                            $field = $filter['field'];
                            $value = "'" . $filter['value'] . "%' ";
                            break;
                        case 'contains':
                            $compare = " LIKE ";
                            $field = $filter['field'];
                            $value = " '%" . $filter['value'] . "%' ";
                            break;
                        case 'doesnotcontain':
                            $compare = " NOT LIKE ";
                            $field = $filter['field'];
                            $value = " '%" . $filter['value'] . "%' ";
                            break;
                        case 'endswith':
                            $compare = " LIKE ";
                            $field = $filter['field'];
                            $value = "'%" . $filter['value'] . "' ";
                            break;
                        case 'eq':
                            $compare = " = ";
                            $field = $filter['field'];
                            $value = "'" . $filter['value'] . "'";
                            break;
                        case 'gt':
                            $compare = " > ";
                            $field = $filter['field'];
                            $value = $filter['value'];
                            break;
                        case 'lt':
                            $compare = " < ";
                            $field = $filter['field'];
                            $value = $filter['value'];
                            break;
                        case 'gte':
                            $compare = " >= ";
                            $field = $filter['field'];
                            $value = $filter['value'];
                            break;
                        case 'lte':
                            $compare = " <= ";
                            $field = $filter['field'];
                            $value = $filter['value'];
                            break;
                        case 'neq':
                            $compare = " <> ";
                            $field = $filter['field'];
                            $value = "'" . $filter['value'] . "'";
                            break;
                    }               
                    if ( $count == 0 && $intcount == 0 ) {
                        $before = "";
                        $end = " " . $filters['logic'] . " ";
                    } elseif ( $count > 0 && $intcount == 0 ) {
                        $before = "";
                        $end = " " . $filters['logic'] . " ";
                    } else {
                        $before = " " . $filters['logic'] . " ";
                        $end = "";
                    }       
                    $where .= ( $nobegin ? "" : $before ) . $field . $compare . $value . ( $noend ? "" : $end );
                    $count ++;
                    $intcount ++;
                } else {
                    $where .= " ( " . parseFilters( $filter , $count ) . " )" ;       
                }     
                $where = str_replace( " or  or " , " or " , $where );
                $where = str_replace( " and  and " , " and " , $where );
            }
        } else {
                $where = " 1 = 1 ";
        }
        return $where;
    }
     
    function oneline( $sql ) {
        $str = $sql;   
        $str = str_replace("\n\r", '<br />', $str );
        $str = str_replace("\r\n", '<br />', $str );
        $str = preg_replace("/[ ]+/", ' ', $str );
        $str = str_replace("\t", '', $str );
        return trim($str);
    }

Back to Top