jQuery grid with PHP & MySQL

Important: This topic was written for older version v1.1.3 of the grid. Some examples may not work with the new version due to differences in the API. New version has easier way of integration with remote scripts, consult the demos for live examples of the new version. Anyway the basic concepts are still relevant and it makes a good read.

This tutorial serves to learn integration of jQuery grid with PHP using open standards based on RESTful API. We would go through every step but it's assumed that the user has basic working knowledge of PHP and MySQL and the user has already set up working environment vis-a-vis PHP/MySQL, so this post won't discuss the installation of PHP, MySQL, Apache/ IIS on Windows, Linux or any other OS.

Complete code discussed in this tutorial can be downloaded from Github via this url: https://github.com/paramquery/grid.

For convenience and to separate client side code from server side code, we would keep 2 main files for every topic:

• index.php (for client side code)
• remote.php (for server side code)

Get JSON data from PHP using associative arrays

In our first topic we would discuss the steps for getting JSON data from PHP for jQuery grid using associative arrays. This is how our client side file index.php looks like where include.php contains the jQuery and pqGrid include files.

<?php
require_once '../include.php';
?>

<script class="ppjs">
    $(function () {
        var colM = [
            { title: "Order ID", width: 100, dataIndx: "OrderID" },            
            { title: "Customer Name", width: 130, dataIndx: "CustomerName" },
            { title: "Product Name", width: 190, dataIndx: "ProductName" },
            { title: "Unit Price", width: 100, dataIndx: "UnitPrice", align: "right" },
            { title: "Quantity", width: 100, dataIndx: "Quantity", align:"right" },            
            { title: "Order Date", width: 100, dataIndx: "OrderDate"},
            { title: "Required Date", width: 100, dataIndx: "RequiredDate" },
            { title: "Shipped Date", width: 100, dataIndx: "ShippedDate" },
            { title: "ShipCountry", width: 100, dataIndx: "ShipCountry" },
            { title: "Freight", width: 100, align: "right", dataIndx: "Freight" },
            { title: "Shipping Name", width: 120, dataIndx: "ShipName" },
            { title: "Shipping Address", width: 180, dataIndx: "ShipAddress" },
            { title: "Shipping City", width: 100, dataIndx: "ShipCity" },
            { title: "Shipping Region", width: 110, dataIndx: "ShipRegion" },
            { title: "Shipping Postal Code", width: 130, dataIndx: "ShipPostalCode" }
        ];
        var dataModel = {
            location: "remote",                        
            dataType: "JSON",
            method: "GET",
            getUrl : function () {                
                return { url: 'remote.php'};
            },
            getData: function ( response ) {                
                return { data: response };                
            }
        };
        $("div#grid_php").pqGrid({ width: 900, height: 400,
            dataModel: dataModel,
            colModel: colM,  
            bottomVisible: false,
            title: "Shipping Orders"
        });
    });
</script>

Note that when we fetch remote data from server, first of all we mention dataModel.location as remote. So dataModel.location remains remote for all the following topics on remote paging, sorting and filtering. jQuery grid uses dataModel.getUrl callback of paramQuery grid to compose our request to server. In this callback we return a plain javascript object containing url and data. url can be absolute or relative and data is optional. data is sent to the url in the form of GET or POST request depending upon dataModel.method. Request is actually send to the server during initialization of the grid and whenever refreshDataAndView method is called.

    var dataModel = {
        location: "remote",                        
        dataType: "JSON",
        method: "GET",
        getUrl : function () {                
            return { url: 'remote.php'};
        },
        getData: function ( response ) {                
            return { data: response };                
        }
    }

On the server side in PHP, we use json_encode to serialize multidimensional array i.e., $products into a string and send it as HTTP response.

<?php

$products = array();

$products[0] = array("OrderID"=>"10248","CustomerName"=>"Vins et alcools Chevalier",
    "ProductName"=>"Mozzarella di Giovanni","UnitPrice"=>"34.8000","Quantity"=>"5",
    "OrderDate"=>"1996-07-04 00:00:00","RequiredDate"=>"1996-08-01 00:00:00","ShippedDate"=>"1996-07-16 00:00:00",
    "ShipCountry"=>"France","Freight"=>"32.3800","ShipName"=>"Vins et alcools Chevalier",
    "ShipAddress"=>"59 rue de l-Abbaye","ShipCity"=>"Reims","ShipRegion"=>"null","ShipPostalCode"=>"51100");

$products[1] = array("OrderID"=>"10248","CustomerName"=>"Vins et alcools Chevalier",
    "ProductName"=>"Singaporean Hokkien Fried Mee","UnitPrice"=>"9.8000","Quantity"=>"10",
    "OrderDate"=>"1996-07-04 00:00:00","RequiredDate"=>"1996-08-01 00:00:00","ShippedDate"=>"1996-07-16 00:00:00",
    "ShipCountry"=>"France","Freight"=>"32.3800","ShipName"=>"Vins et alcools Chevalier",
    "ShipAddress"=>"59 rue de l-Abbaye","ShipCity"=>"Reims","ShipRegion"=>"null","ShipPostalCode"=>"51100");

echo json_encode($products);

?>

Get data from database with PHP & MySQL

Now we would discuss getting data for jQuery grid from database using PHP and MySQL. We would take the help of PDO extension for PHP which is upto the task that allows us to:

• support various databases.
• write parameterized queries to safeguard against SQL injection attacks.
• support utf-8 characters required for internationalization.

We would also take the help of json_encode and json_decode PHP functions to serialize and deserialize data. In the context of jQuery grid

• json_encode: serializes array of rows into a string
• json_decode: deserializes string into array of rows.

where each row is represented as an associative array.

First we write config.php file which contains database connection parameters. Also we add 2 utility functions to it which we would use in this and all the subsequent topics:

//config.php

// mysql example

define('DB_HOSTNAME','localhost'); // database host name
define('DB_USERNAME', 'username');     // database user name
define('DB_PASSWORD', 'password'); // database password
define('DB_NAME', 'northwind'); // database name

function getDataBaseHandle(){
    $dsn = 'mysql:host='.DB_HOSTNAME.';dbname='.DB_NAME;
    $options = array(
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
    ); 
    $dbh = new PDO($dsn, DB_USERNAME, DB_PASSWORD, $options);
}
//check every column name
function isValidColumn($dataIndx){
    if (preg_match('/^[a-z,A-Z]*$/', $dataIndx))
    {
        return true;
    }
    else
    {
        return false;
    }    
}

and this is how we write our server side code in PHP. The important points to note here are PDO::FETCH_ASSOC and json_encode.
$stmt->fetchAll(PDO::FETCH_ASSOC) returns an array of rows i.e., associative arrays.

<?php

    require_once '../conf.php';

    $dbh = getDatabaseHandle();
                
    $sql = "Select OrderID,CustomerName,ProductName,UnitPrice,Quantity,
            OrderDate,RequiredDate,ShippedDate,ShipCountry,Freight,ShipName,
            ShipAddress,ShipCity,ShipRegion,ShipPostalCode from invoices order by orderID limit 0,100";
    
    $stmt = $dbh->query($sql);    
    $products = $stmt->fetchAll(PDO::FETCH_ASSOC);

    echo json_encode($products);
?>

Paging for jQuery Grid with PHP & MySQL

Local paging is inbuilt into the grid and we don't need to write any PHP code for that other than the code to fetch data from database which we already covered in previous topic. However we have to write paging logic in PHP when we need remote server side paging. jQuery grid sends 2 important parameters rPP and curPage required for paging to the server as POST variables. rPP means results per page and curPage denotes current page in the jQuery grid. They can also be send as GET variables when dataModel.method = "GET". Note the inclusion of paging: 'remote' and curPage: 1 properties in dataModel.

var dataModel = {
    location: "remote",
    paging: "remote",
    curPage: 1,
    dataType: "JSON",
    method: "POST",
    getUrl: function (ui) {
        return {
            url: "remote.php",
            data: {
                pq_rpp: this.rPP,
                pq_curpage: this.curPage
            }
        }
    },
    getData: function (dataJSON) {
        return { curPage: dataJSON.curPage, totalRecords: dataJSON.totalRecords, data: dataJSON.data };
    }
}

and on the server side we write this code in PHP.

<?php

require_once '../conf.php';

if(isset($_POST["pq_curpage"]) && isset($_POST["pq_rpp"]) )
{
    $pq_curPage = $_POST["pq_curpage"];    
    
    $pq_rPP=$_POST["pq_rpp"];
    
    $sql = "Select count(*) from invoices";
    
    $dbh = getDatabaseHandle();
    $stmt = $dbh->query($sql);    
    $total_Records = $stmt->fetchColumn();
    
    $skip = pageHelper($pq_curPage, $pq_rPP, $total_Records);
                    
    $sql = "Select OrderID,CustomerName,ProductName,UnitPrice,Quantity,
            OrderDate,RequiredDate,ShippedDate,ShipCountry,Freight,ShipName,
            ShipAddress,ShipCity,ShipRegion,ShipPostalCode from invoices order by orderID limit ".$skip." , ".$pq_rPP;
    $stmt = $dbh->query($sql);    
    $products = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $sb = "{\"totalRecords\":" . $total_Records . ",\"curPage\":" . $pq_curPage . ",\"data\":".json_encode($products)."}";
    echo $sb;    
}    
?>

Sorting for jQuery Grid with PHP & MySQL

Local sorting is inbuilt into the grid and we don't need to write any PHP code for that other than the code to fetch data from database which we already covered in one of the previous topics. However we have to write sorting logic in PHP when we need remote server side sorting. jQuery grid sends 2 important parameters sortIndx and sortDir required for sorting to the server as POST variables. Note the inclusion of sorting: 'remote' and sortIndx: "OrderID" properties in dataModel.

    var dataModel = {
        location: "remote",            
        sorting:"remote",
        dataType: "JSON",
        method: "POST",
        sortIndx: "OrderID",            
        getUrl: function(ui){
            return {
                url: "remote.php",
                data:{
                    sortIndx:this.sortIndx,
                    sortDir:this.sortDir
                }
            }
        },
        getData: function ( data ) {                
            return { data: data };                
        }
    }

and on the server side we write this code in PHP.

<?php

require_once '../conf.php';

if(isset($_POST["sortDir"]) && isset($_POST["sortIndx"]) )
{
    $sortIndx = $_POST["sortIndx"];
    if(isValidColumn($sortIndx)==false){
        throw("invalid column");
    }
    $sortDir = $_POST["sortDir"];
    $sortDir=($sortDir=="up")?"asc":"desc";
        
    $dbh = getDatabaseHandle();
    
    $sql = "Select OrderID,CustomerName,ProductName,UnitPrice,Quantity,
            OrderDate,RequiredDate,ShippedDate,ShipCountry,Freight,ShipName,
            ShipAddress,ShipCity,ShipRegion,ShipPostalCode from invoices order by ".$sortIndx."  ".$sortDir;

    //echo $sql;
    $stmt = $dbh->query($sql);    
    $products = $stmt->fetchAll(PDO::FETCH_ASSOC);

    echo json_encode($products);    
}    
?>

Simultaneous Paging and Sorting for jQuery Grid with PHP & MySQL

In this post we would discuss simultaneous paging and sorting on server with PHP. For this we require to combine the parameters of the previous 2 posts. So in this case jQuery grid sends 4 parameters pq_rpp, pq_curpage, sortIndx and sortDir to the server as POST variables. They can also be send as GET variables when dataModel.method = "GET".

var dataModel = {
    location: "remote",            
    paging:"remote",
    curPage:1,
    sorting:"remote",
    dataType: "JSON",
    method: "POST",
    sortIndx: "OrderID",
    getUrl: function(){
        return {
            url: "remote.php",
            data:{
                pq_curpage:this.curPage,
                pq_rpp:this.rPP,
                sortIndx:this.sortIndx,
                sortDir:this.sortDir
            }
        }
    },
    getData: function ( dataJSON ) {                
        return { curPage: dataJSON.curPage, totalRecords: dataJSON.totalRecords, data: dataJSON.data };                
    }
}

and on the server side we write this code in PHP.

<?php

    require_once '../conf.php';

if(isset($_POST["sortDir"]) && isset($_POST["sortIndx"]) )
{
    $sortIndx = $_POST["sortIndx"];
    if(isValidColumn($sortIndx)==false){
        throw("invalid sort column");
    }        
    $sortDir = $_POST["sortDir"];
    $sortDir=($sortDir=="up")?"asc":"desc";
    
    $pq_curPage = $_POST["pq_curpage"];        
    $pq_rPP=$_POST["pq_rpp"];
    
    $sql = "Select count(*) from invoices";
    
    $dbh = getDatabaseHandle();
    $stmt = $dbh->query($sql);    
    $total_Records = $stmt->fetchColumn();
    
    $skip = pageHelper($pq_curPage, $pq_rPP, $total_Records);
    
    $sql = "Select OrderID,CustomerName,ProductName,UnitPrice,Quantity,
            OrderDate,RequiredDate,ShippedDate,ShipCountry,Freight,ShipName,
            ShipAddress,ShipCity,ShipRegion,ShipPostalCode from invoices order by ".$sortIndx."  ".$sortDir.
            " limit ".$skip." , ".$pq_rPP;

    //echo $sql;
    $stmt = $dbh->query($sql);    
    $products = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $sb = "{\"totalRecords\":" . $total_Records . ",\"curPage\":" . $pq_curPage . ",\"data\":".json_encode($products)."}";
    echo $sb;
}    
?>

Filtering for jQuery Grid with PHP & MySQL

In this post we would discuss filtering for jQuery grid on server with PHP. We add a textbox and select list to the toolbar in render callback. Then we add 2 custom fields in dataModel i.e., filterIndx & filterValue to hold dataIndx and filtered input by the user. And we post these 2 variables to the server in dataModel.getUrl callback.

//add textbox and select list in the toolbar
    obj.render = function (evt, obj) {
        var $toolbar = $("
").appendTo($(".pq-grid-top", this));
 
        $("Filter").appendTo($toolbar);
 
        $("").appendTo($toolbar)
            .change(function (evt) {            
                pqFilter.search();            
        });
 
        $("\
        Ship Country\
        Customer Name\
        ").appendTo($toolbar)
           .change(function () {
            pqFilter.search();
        });
        $("").appendTo($toolbar);
 
    };
//define dataModel.
    var dataModel = {
        location: "remote",
        dataType: "JSON",
        method: "POST",
        paging: "local",
        filterIndx: "",
        filterValue: "",
        getUrl: function () {
            var data = {};
            if (this.filterIndx && this.filterValue ) {
                  data['filterIndx']=this.filterIndx;
                  data['filterValue']=this.filterValue;
            }
            var obj = { url: "remote.php", data: data };
            return obj;
        },
        getData: function (response) {
            return { data: response };
        }
    }

and on the server side we write this code in PHP.

<?php
    require_once '../conf.php';

    $where = "";
    $filterValue ="";
    if(isset($_POST["filterIndx"]) && isset($_POST["filterValue"]) )
    {
        $filterIndx = $_POST["filterIndx"];
        if(isValidColumn($filterIndx)==false){
            throw("invalid filter column");
        }
        $filterValue = $_POST["filterValue"];
        $where  = " where ".$filterIndx." like CONCAT('%', ?, '%')";
    }            
        
    $dbh = getDatabaseHandle();
    
    $sql = "Select OrderID,CustomerName,ProductName,UnitPrice,Quantity,
            OrderDate,RequiredDate,ShippedDate,ShipCountry,Freight,ShipName,
            ShipAddress,ShipCity,ShipRegion,ShipPostalCode from invoices ".
            $where.
            " order by OrderID";

    //echo $sql;
    $stmt = $dbh->prepare($sql);
    $stmt->execute(array($filterValue));    
    $products = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo json_encode($products);    
?>

Filtering and Paging for jQuery Grid with PHP & MySQL

In this post we would discuss filtering with remote paging for jQuery grid on server with PHP. We add a textbox and select list to the toolbar in render callback similar to previous topic. Then we add 2 custom fields in dataModel i.e., filterIndx & filterValue to hold dataIndx and filtered input by the user. We post 2 filter variables and 2 paging variables to the server in dataModel.getUrl callback.

//define dataModel
    var dataModel = {
        location: "remote",
        paging: "remote",
        dataType: "JSON",
        method: "POST",
        curPage: 1,
        filterIndx: "",
        filterValue: "",
        getUrl: function () {
            var data = {
                pq_curpage: this.curPage,
                pq_rpp: this.rPP};
            if (this.filterIndx && this.filterValue ) {
                data['filterIndx']=this.filterIndx;
                data['filterValue']=this.filterValue;
            }
            var obj = { url: "remote.php", data: data };
            //debugger;
            return obj;
        },
        getData: function ( dataJSON ) {                
            return { curPage: dataJSON.curPage, totalRecords: dataJSON.totalRecords, data: dataJSON.data };
        }

and on the server side we write this code in PHP.

<?php
    require_once '../conf.php';

    $where = "";
    $filterValue ="";
    if(isset($_POST["filterIndx"]) && isset($_POST["filterValue"]) )
    {
        $filterIndx = $_POST["filterIndx"];
        if(isValidColumn($filterIndx)==false){
            throw("invalid filter column");
        }
        $filterValue = $_POST["filterValue"];        
        $where  = " where ".$filterIndx." like CONCAT('%', ?, '%')";
    }            
    $dbh = getDatabaseHandle();
    
    //count filtered records.
    $sql = "Select count(*) from invoices ".
            $where;            

    $pq_curPage = $_POST["pq_curpage"];    
    
    $pq_rPP=$_POST["pq_rpp"];
            
    $stmt = $dbh->prepare($sql);    
    $stmt->execute(array($filterValue));
    
    $total_Records = $stmt->fetchColumn();
    
    $skip = pageHelper($pq_curPage, $pq_rPP, $total_Records);

    $sql = "Select OrderID,CustomerName,ProductName,UnitPrice,Quantity,
            OrderDate,RequiredDate,ShippedDate,ShipCountry,Freight,ShipName,
            ShipAddress,ShipCity,ShipRegion,ShipPostalCode from invoices ".
            $where.
            " order by OrderID limit ".$skip." , ".$pq_rPP;
        
    //$stmt = $dbh->query($sql);    
    $stmt = $dbh->prepare($sql);
    $stmt->execute(array($filterValue));
            
    $products = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $sb = "{\"totalRecords\":" . $total_Records . ",\"curPage\":" . $pq_curPage . ",\"data\":".json_encode($products)."}";
    echo $sb;
?>