jQuery grid editing examples with PHP & MySQL

Saving changes made by a user in the grid into a database is one of the most commonly required tasks in an application. This topic is written to delve deeper into the server side code of all editing examples ( auto save, batch editing and row editing ) using PHP and MySQL. This tutorial is applicable to both free and Pro version.

As with any other ParamQuery example, RESTful API is used for client server interaction. This example uses Products Table from the Northwind database.

We use 2 files for PHP code:

Products table schema

If you are able to download Northwind database from the internet, then good otherwise create this schema for Products table. 
    CREATE TABLE `products` (
      `ProductID` int(11) NOT NULL AUTO_INCREMENT,
      `ProductName` varchar(40) DEFAULT NULL,
      `SupplierID` int(11) DEFAULT NULL,
      `CategoryID` int(11) DEFAULT NULL,
      `QuantityPerUnit` varchar(20) DEFAULT NULL,
      `UnitPrice` decimal(10,4) DEFAULT '0.0000',
      `UnitsInStock` smallint(6) DEFAULT '0',
      `UnitsOnOrder` smallint(6) DEFAULT '0',
      `ReorderLevel` smallint(6) DEFAULT '0',
      `Discontinued` tinyint(4) DEFAULT NULL,
      PRIMARY KEY (`ProductID`)
    ) ENGINE=MyISAM AUTO_INCREMENT=96 DEFAULT CHARSET=utf8$$
Note that:

PHP code

There are few common functions used throughout the code 
function getDBH(){
    $dsn = 'mysql:host='.DB_HOSTNAME.';dbname='.DB_NAME;
    $options = array(
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
        PDO::ATTR_EMULATE_PREPARES => false //to get native data types from prepared statements.(> PHP 5.3 && mysqlnd)
    );
    $dbh = new PDO($dsn, DB_USERNAME, DB_PASSWORD, $options);
    return $dbh;
}
//for mapping of boolean values to TINYINT column in db.
function boolToInt($val){
    //return $val;
    if($val=='true'){
        return 1;
    }
    else if($val =='false'){
        return 0;
    }
}
//for mapping of number to booleans.
function intToBool($val){
    if($val==1){
        return true;
    }
    else if($val ==0){
        return false;
    }
}

getDBH is used to get database connection with PDO. Note that it uses PDO::ATTR_EMULATE_PREPARES => false to get native data types from prepared statements without which all our values in JSON data are strings enclosed by double quotes.

boolToInt is used to map boolean values received from grid to tinyInt values for Discontinued column.

intToBool is used for the reverse.

Serving data

To serve JSON data from database to grid, we write this PHP code. We require boolean values for Discontinued column in grid, so we iterate through all rows with foreach loop to convert 1 to true and 0 to false. 
    $sql = "Select ProductID, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock, Discontinued from products order by ProductID";

    $dbh = getDBH();

    $stmt = $dbh->prepare($sql);
    $stmt->execute();
    $products = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($products as $i=> &$row)
    {
        $row['Discontinued']= intToBool($row['Discontinued']);
    }

    $response =  "{\"data\":".json_encode($products)."}";

To serve paged data ( used in Row editing example ) in the grid, we write this PHP code. where pq_curpage and pq_rpp are $_GET variables send by grid.

pq_curpage denotes current page number while pq_rpp denotes records per page. 

if( isset($_GET["pq_curpage"]) )//paging.
{
    $pq_curPage = $_GET["pq_curpage"];
    $pq_rPP=$_GET["pq_rpp"];

    $sql = "Select count(*) from products";

    $dbh = getDBH();
    $stmt = $dbh->query($sql);
    $total_Records = $stmt->fetchColumn();

    $skip = ($pq_rPP * ($pq_curPage - 1));

    if ($skip >= $total_Records)
    {
        $pq_curPage = ceil($total_Records / $pq_rPP);
        $skip = ($pq_rPP * ($pq_curPage - 1));
    }

    $sql = "Select ProductID, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock, Discontinued from products
            order by ProductID limit ".$skip." , ".$pq_rPP;
    $stmt = $dbh->query($sql);
    $products = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($products as $i=> &$row)
    {
        $row['Discontinued']= intToBool($row['Discontinued']);
    }

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

Saving into database

$_POST['list'] is posted by $.ajax call from saveChanges function in javascript code. $_POST['list'] is an object comprising of three other array lists: 
else if( isset($_POST["list"]))
{
    $dlist = $_POST['list'];

    if(isset($dlist["updateList"])){
        updateList($dlist["updateList"]);
    }
    if(isset($dlist["addList"])){
        $dlist["addList"] = addList($dlist["addList"]);
    }
    if(isset($dlist["deleteList"])){
        deleteList($dlist["deleteList"]);
    }

    $response =  json_encode($dlist);
}

We call the functions corresponding to the array lists, addList() returns the modified addList array which contains id of the newly added rows.

We send back the whole list after json encoding it as $response back to grid so that grid can update the id of the newly added rows. 

//add multiple records in db.
function addList($addList)
{
    $pdo = getDBH();
    foreach ($addList as &$r)
    {
        $r['ProductID'] = addSingle($pdo, $r);
    }
    return $addList;
}
//update multiple records in db.
function updateList($updateList)
{
    $pdo = getDBH();
    foreach ($updateList as $r)
    {
        updateSingle($pdo, $r);
    }
}
//delete multiple records in db.
function deleteList($deleteList)
{
    $pdo = getDBH();
    foreach ($deleteList as $r)
    {
        deleteSingle($pdo, $r);
    }
}
And these in turn call the functions used to manipulate one record at a time in the database. 
//add single record in db.
function addSingle($pdo, $r){
    $discontinued = boolToInt($r['Discontinued']);

    $sql = "insert into products (ProductName, QuantityPerUnit, UnitPrice, UnitsInStock, Discontinued) values (?, ?, ?, ?, ?)";

    $stmt = $pdo->prepare($sql);
    $result = $stmt->execute(array($r['ProductName'], $r['QuantityPerUnit'], $r['UnitPrice'], $r['UnitsInStock'], $discontinued));

    if($result == false) {
        throw new Exception(print_r($stmt->errorInfo(),1).PHP_EOL.$sql);
    }
    return $pdo->lastInsertId();
}
//update single record in db.
function updateSingle($pdo, $r){
    $discontinued = boolToInt($r['Discontinued']);

    $sql = "update products set ProductName = ?, QuantityPerUnit = ?, UnitPrice = ?, UnitsInStock = ?, Discontinued = ? where ProductID = ?";

    $stmt = $pdo->prepare($sql);
    $result = $stmt->execute(array($r['ProductName'], $r['QuantityPerUnit'], $r['UnitPrice'], $r['UnitsInStock'], $discontinued,  $r['ProductID']));

    if($result == false) {
        throw new Exception(print_r($stmt->errorInfo(),1).PHP_EOL.$sql);
    }
}
//delete single record from db.
function deleteSingle($pdo, $r)
{
    $sql = "delete from products where ProductID = ?";

    $stmt = $pdo->prepare($sql);
    $result = $stmt->execute(array( $r['ProductID']));

    if($result == false) {
        throw new Exception(print_r($stmt->errorInfo(),1).PHP_EOL.$sql);
    }
}
Above single record manipulation functions are also used by row editing example where pq_add is send by grid when single row is added, pq_update is sent by grid when single row is updated and pq_delete is sent when single record is deleted. 
if( isset($_GET["pq_add"]))
{
    $response = "{\"recId\": \"" . addSingle(getDBH(), $_GET ). "\"}";
}
else if( isset($_GET["pq_update"]))
{
    updateSingle(getDBH(), $_GET);
    $response =  "{\"result\": \"success\"}";
}
else if( isset($_GET["pq_delete"]))
{
    deleteSingle(getDBH(), $_GET);
    $response =  "{\"result\": \"success\"}";
}

Response back to grid

And lastly we send back all kind of responses back to grid. 
    echo $response;

Complete code:

 'SET NAMES utf8',
        PDO::ATTR_EMULATE_PREPARES => false //to get native data types from prepared statements.(> PHP 5.3 && mysqlnd)
    );
    $dbh = new PDO($dsn, DB_USERNAME, DB_PASSWORD, $options);
    return $dbh;
}
//for mapping of boolean values to TINYINT column in db.
function boolToInt($val){
    //return $val;
    if($val=='true'){
        return 1;
    }
    else if($val =='false'){
        return 0;
    }
}
//for mapping of number to booleans.
function intToBool($val){
    if($val==1){
        return true;
    }
    else if($val ==0){
        return false;
    }
}
//add single record in db.
function addSingle($pdo, $r){
    $discontinued = boolToInt($r['Discontinued']);

    $sql = "insert into products (ProductName, QuantityPerUnit, UnitPrice, UnitsInStock, Discontinued) values (?, ?, ?, ?, ?)";

    $stmt = $pdo->prepare($sql);
    $result = $stmt->execute(array($r['ProductName'], $r['QuantityPerUnit'], $r['UnitPrice'], $r['UnitsInStock'], $discontinued));

    if($result == false) {
        throw new Exception(print_r($stmt->errorInfo(),1).PHP_EOL.$sql);
    }
    return $pdo->lastInsertId();
}
//update single record in db.
function updateSingle($pdo, $r){
    $discontinued = boolToInt($r['Discontinued']);

    $sql = "update products set ProductName = ?, QuantityPerUnit = ?, UnitPrice = ?, UnitsInStock = ?, Discontinued = ? where ProductID = ?";

    $stmt = $pdo->prepare($sql);
    $result = $stmt->execute(array($r['ProductName'], $r['QuantityPerUnit'], $r['UnitPrice'], $r['UnitsInStock'], $discontinued,  $r['ProductID']));

    if($result == false) {
        throw new Exception(print_r($stmt->errorInfo(),1).PHP_EOL.$sql);
    }
}
//delete single record from db.
function deleteSingle($pdo, $r)
{
    $sql = "delete from products where ProductID = ?";

    $stmt = $pdo->prepare($sql);
    $result = $stmt->execute(array( $r['ProductID']));

    if($result == false) {
        throw new Exception(print_r($stmt->errorInfo(),1).PHP_EOL.$sql);
    }
}
//add multiple records in db.
function addList($addList)
{
    $pdo = getDBH();
    foreach ($addList as &$r)
    {
        $r['ProductID'] = addSingle($pdo, $r);
    }
    return $addList;
}
//update multiple records in db.
function updateList($updateList)
{
    $pdo = getDBH();
    foreach ($updateList as $r)
    {
        updateSingle($pdo, $r);
    }
}
//delete multiple records in db.
function deleteList($deleteList)
{
    $pdo = getDBH();
    foreach ($deleteList as $r)
    {
        deleteSingle($pdo, $r);
    }
}
//end of functions.

if( isset($_GET["pq_add"]))
{
    $response = "{\"recId\": \"" . addSingle(getDBH(), $_GET ). "\"}";
}
else if( isset($_GET["pq_update"]))
{
    updateSingle(getDBH(), $_GET);
    $response =  "{\"result\": \"success\"}";
}
else if( isset($_GET["pq_delete"]))
{
    deleteSingle(getDBH(), $_GET);
    $response =  "{\"result\": \"success\"}";
}
else if( isset($_GET["pq_batch"]))
{
    $dlist = $_POST['list'];

    if(isset($dlist["updateList"])){
        updateList($dlist["updateList"]);
    }
    if(isset($dlist["addList"])){
        $dlist["addList"] = addList($dlist["addList"]);
    }
    if(isset($dlist["deleteList"])){
        deleteList($dlist["deleteList"]);
    }

    $response =  json_encode($dlist);
}
else if( isset($_GET["pq_curpage"]) )//paging.
{
    $pq_curPage = $_GET["pq_curpage"];
    $pq_rPP=$_GET["pq_rpp"];

    $sql = "Select count(*) from products";

    $dbh = getDBH();
    $stmt = $dbh->query($sql);
    $total_Records = $stmt->fetchColumn();

    $skip = ($pq_rPP * ($pq_curPage - 1));

    if ($skip >= $total_Records)
    {
        $pq_curPage = ceil($total_Records / $pq_rPP);
        $skip = ($pq_rPP * ($pq_curPage - 1));
    }

    $sql = "Select ProductID, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock, Discontinued from products
            order by ProductID limit ".$skip." , ".$pq_rPP;
    $stmt = $dbh->query($sql);
    $products = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($products as $i=> &$row)
    {
        $row['Discontinued']= intToBool($row['Discontinued']);
    }

    $sb = "{\"totalRecords\":" . $total_Records . ",\"curPage\":" . $pq_curPage . ",\"data\":".json_encode($products)."}";
    $response =  $sb;
}
else{
    $sql = "Select ProductID, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock, Discontinued from products order by ProductID";

    $dbh = getDBH();

    $stmt = $dbh->prepare($sql);
    $stmt->execute();
    $products = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($products as $i=> &$row)
    {
        $row['Discontinued']= intToBool($row['Discontinued']);
    }

    $response =  "{\"data\":".json_encode($products)."}";
}
echo $response;
?>

Have a question on this? Please post it in the forum.