Skip to content

Edit class (CRUD)

Overview

The Edit class extends Export class class. All functionality that the jqGrid class and Export class exposes is also available in the Edit class.

The main purpose of this class is to perform CRUD (Create, Retrieve, Update, Delete) operations automatically. These operations can be performed only when a database table is defined and associated with the jqGrid instance. Another important thing to note is that the table must have at least one primary unique key. The key can be any table column with unique value and/or serial key (also known as auto-increment in most databases like MySQL for example).

Using this class requires some JavaScript knowledge, since the grid should be created in an existing HTML or PHP page with JavaScript declaration. In other words, the Edit class will only take care of the server side operations, while all other events and operations at the client side can be defined manually by the developer. More information on jqGrid JavaScript documentation can be found here

For code without to write javascript and html code please look at Render class.

Methods and variables

All the available public methods and variables can be read here

Transactions

By default all CRUD operations in Guriddo jqGrid are performed with transaction enabled (if the underlaying database support it). The public variable that control this is trans and its default value is true. To disable transactions on insert, update or delete set it to false (In case of using the Edit class set it before the methods of insert, update or delete and in case of using it in Render class set it before render method )

// Create the jqGrid Edit instance
$grid = new Guriddo\jqGrid\Edit\Edit($conn, 'pdo');
...
// disable transaction
$grid->trans = false;
...
// do here CRUD

Validation of input data

Because of the nature of the library, which contain PHP and JavaScript code we have two independent type of input data validations - validations at client using JavaScript and validations at server using PHP. These two type of validations can be configured to work individually or thogether.

To configure JavaScript validation please refer to the following information

In this chapter we will consider server side PHP validation and sanitation.

The server side validation in Guriddo jqGrid PHP is performed via the [Validator] class. Description of public methods and variables of the class can be [read from here].

To enable the validation set the public variable serverValidate to true. To enable sanitation set the public variable serverSanitize to true. By default these are disabled.

...
    $grid->serverValidate = true; // enable validation
    $grid->serverSanitize = true; // enable sanitation 
...

Note

In case of setting both Valiadation and Sanitation to true the order of executing is: first is executed Validation and then Sanitation.

Additionally to this it is needed to configure the two array to describe the rules of validation and sanitation. Setting of the arrays is done with the following methods : setValidationRules and setSanitatationRules It is not necessary that the length of the array is equal of those from the grid colModel - here we can validate only certain fields.

Typical the the validation array cam look like this:

$validations = array(
    'name' => array("required"=>true),
    'email' => array('email'=>true, "required"=>true),
    'alias' => array('anything'=>true),
    'pwd'=>array('anything'=>true),
    'gsm' => array('phone'=>true),
    'birthdate' => array('date'=>true, "format"=>"d/m/Y"),
    'price'=>array("float"=>true,"minValue"=>10,"maxValue"=>50),
    'qty'=>array("int"=>true,"minValue"=>1)
);
$grid->setValidationRules( $validations );

Additionally to the standard types used in PHP - number, float, int, integer, boolean, email, url, date, time, datetime, ip we have defined a custom types where the check is perforemed with regular expression. This array of definitions is public and it can be extended and configured depending on the requirements of the project. The array has the following items:

public static $regexes = Array(
    'date' => "^[0-9]{4}[-/][0-9]{1,2}[-/][0-9]{1,2}\$",
    'datetime' => "^[0-9]{4}[-/][0-9]{1,2}[-/][0-9]{1,2}\$",
    'time' => "^[0-9]{2}[:/][0-9]{1,2}[:/][0-9]{1,2}\$",
    'alfanum' => "^[0-9a-zA-Z ,.-_\\s\?\!]+\$",
    'not_empty' => "[a-z0-9A-Z]+",
    'words' => "^[A-Za-z]+[A-Za-z \\s]*\$",
    'phone' => "^[0-9]{10,11}\$",
    'zipcode' => "^[1-9][0-9]{3}[a-zA-Z]{2}\$",
    'plate' => "^([0-9a-zA-Z]{2}[-]){2}[0-9a-zA-Z]{2}\$",
    '2digitopt' => "^\d+(\,\d{2})?\$",
    '2digitforce' => "^\d+\,\d\d\$",
    'anything' => "^[\d\D]{0,}\$", //incl empty
    'string'=> "^[\d\D]{0,}\$",
    'text'=> "^[\d\D]{0,}\$"
);

The sanitize array contain only the name(s) of the field(s) which will be sanitized. The other information is get from the validation array. This mean that if there is a plan to use only sanitation the validation array should be configured as well.

...
    $sanatize = array('email, ''alias', 'price');
    $grid->setSanitatationRules( $sanatize );
...

It is possible to use only the validation array for validation and sanitation setting the property sanitize in validation array to true. Using the validation code above we can set only one array:

$grid->serverValidate = true; // enable validation
$grid->serverSanitize = true; // enable sanitation 
$validations = array(
    'name' => array("required"=>true),
    'email' => array('email'=>true, 'required'=>true, 'sanitize'=>true),
    'alias' => array('anything'=>true, 'sanitize'=>true),
    'pwd'=>array('anything'=>true),
    'gsm' => array('phone'=>true),
    'birthdate' => array('date'=>true, "format"=>"d/m/Y"),
    'price'=>array("float"=>true,"minValue"=>10,"maxValue"=>50, 'sanitize'=>true),
    'qty'=>array("int"=>true,"minValue"=>1)
);
$grid->setValidationRules( $validations );

Important

To validate dates it is needed to set the date property to true and the format of the date which the user input it - i.e the input date format.

Other properties that can be used in validation are

  • minValue - the minimal value which can be accepted for integers, floats and dates
  • maxValue - the maximal value which can be accepted for integers, floats and dates
  • required - the values can not be empty for all types

When validation or sanitation fails a error message is send to the user. In form editing the message appear at the top of the edit form, in cell and in-line edit as pop-up window. In this case the edit or add operation fail too.

Note

The validation class can be used the same way in Edit and in Render class

Warning

The Validation class is called internally in Edit and respectively in Render class and the regex expression to extend validations types can not be configured within these classes.

For examples, please check Updating chapter

Logging

In case if there is a problem with the CRUD operations you can enable the Logger in order to see what is done on server when the CRUD is in action.

The debug information can explain a lot of problems.

Note

Be a sure that when using the build-in logger the log file is in directory with write permissions.

Inserting

Inserting a record into the database table with Edit class is quite easy. Three things should be done - specify a table, set the primary key (optional specify if the primary key is serial) and call the method insert with the data posted from the grid.

It is important to note that the data array passed for inserting should contain key elements which correspond to the names in the table. In terms of jqGrid this is a name property in colModel.

We will demonstrate this with example. We will use as base our example:

Let suppose that we want only to insert a record in the orders table. For this purpose we will use the navigator and will enable only add form. Below is only the changed code

<script type="text/javascript">
jQuery(document).ready(function(){
....
// Craeate the grid manually
jQuery("#grid").jqGrid({
"colModel":[
     {"name":"OrderID","index":"OrderID","label":"ID","width":60, "key":true},
     {"name":"OrderDate","index":"OrderDate", editable:true},
     {"name":"CustomerID","index":"CustomerID", editable:true},
     {"name":"Freight","index":"Freight", editable:true},
     {"name":"ShipName","index":"ShipName", editable:true}
],
"url":"grid.php",
"datatype":"json",
"jsonReader":{repeatitems:false},
"pager":"#pager",
// now we should set the url where we post the data
// in this case the same url. 
// if it is not set the url parameter is used
"editurl": "grid.php"
});
// Set navigator with adding record and search enabled.
jQuery("#grid").jqGrid('navGrid','#pager',{add:true,edit:false,del:false});
......
});
</script>

As can be seen we have made a lot of the fields editable setting the property of the grid editable to true.

Another important settings here is in the field OrderID. This field is not editable, because it is defined as serial in the database, so we do not need to post them to the server.
When we do this the field will not appear in the form where we add a data.

Note

In Render class the editing property is set automatically to all fields

The changes in PHP code:

<?php
require_once 'path_to_your_project_vendor_dir/autoload.php';

use Guriddo\Utils\Utils;

// Connection to the server
$conn = new PDO('mysql:host=localhost;dbname=northwind','user','password');
// Tell the db that we use utf-8
$conn->query("SET NAMES utf8");

// Create the jqGrid Edit instance
$grid = new Guriddo\jqGrid\Edit\Edit($conn, 'pdo');
// Write the SQL Query
$grid->SelectCommand = 'SELECT OrderID, OrderDate, CustomerID, Freight, ShipName FROM orders';
$grid->dataType = "json";

if($grid->oper == 'add') {
    // the the table for inserting
   $grid->setTable('orders');
    // set the primary(uniquie) key
   $grid->setPrimaryKeyId('OrderID');
    // obtain the data posted from the client
   $data = filter_input_array(INPUT_POST);
    // try to insert the data in the table
   $grid->insert($data);
} else {
   $grid->queryGrid();
}

From the code it can be seen that the method for inserting is called insert and it accept only one parameter - the data to be inserted in key=>value definition. It is not necessary that all the field values in the data should be equal of those in the table. It is possible to insert only one field.

Tip

Another usefull feature here is that inserting on second (third ...) table(s) is possible - just set it after the first insert and primary key too and do the insert.

Notes on insert

The insert will fail (return false) on the following conditions:

  • The public variable is set to false (ex. $grid->add = false)
  • The database fields can not be obtained from the table
  • If there is no primary key set or detected
  • If server validation is enabled and there a non valid value is detected

Important

Please note that all the PHP and JS code above is automatically generated when a Render class is used.

Updating

Just like inserting updating is performed the same way. We will demonstrate this with example. Again note the changes in the html file. We will add validation and sanitation of certain field and will filter the Fright input.

The HTML file is similar to the inserting except that we will disable inserting and enable editing in the navigator and add a rule which field should be set as id (primary) so that the script recognizes it.

<script type="text/javascript">
jQuery(document).ready(function(){
....
// Craeate the grid manually
jQuery("#grid").jqGrid({
"colModel":[
     {"name":"OrderID","index":"OrderID","label":"ID","width":60, "key":true},
     {"name":"OrderDate","index":"OrderDate", editable:true},
     {"name":"CustomerID","index":"CustomerID", editable:true},
     {"name":"Freight","index":"Freight", editable:true},
     {"name":"ShipName","index":"ShipName", editable:true}
],
"url":"grid.php",
"datatype":"json",
"jsonReader":{repeatitems:false},
"pager":"#pager",
// now we should set the url where we post the data
// in this case the same url. 
// if it is not set the url parameter is used
"editurl": "grid.php"
// tell the grid to post the OrderID as primary key
"prmNames":{"id":"OrderID"}
});
// Set navigator with adding record and search enabled.
jQuery("#grid").jqGrid('navGrid','#pager',{add:false,edit:true,del:false});
......
});
</script>

When we set "prmNames":{"id":"OrderID"} we tell the grid to post the value of the id and the name should be not id, but OrderID. This is true, since we set in the colModel key:true, which indicates that this is the id. Another reason for this is that OrderID is not editable and its value usually will not be posted to the server - but in this case it will be posted.

The related PHP code with server validation is:

<?php
require_once 'path_to_your_project_vendor_dir/autoload.php';

use Guriddo\Utils\Utils;

// Connection to the server
$conn = new PDO('mysql:host=localhost;dbname=northwind','user','password');
// Tell the db that we use utf-8
$conn->query("SET NAMES utf8");

// Create the jqGrid Edit instance
$grid = new Guriddo\jqGrid\Edit\Edit($conn, 'pdo');
// Write the SQL Query
$grid->SelectCommand = 'SELECT OrderID, OrderDate, CustomerID, Freight, ShipName FROM orders';
$grid->dataType = "json";

if($grid->oper == 'edit') {
    // the the table for inserting
    $grid->setTable('orders');
    // set the primary(uniquie) key
    $grid->setPrimaryKeyId('OrderID');
    // obtain the data posted from the client
    $data = filter_input_array(INPUT_POST);
    // enable validation
    $grid->serverValidate = true;
    $grid->serverSanitize = true; 
    // definitions for validation

    $validation =  array(
        "ShipName" => array("string"=>true, "required"=>true, "sanitize"=>true);
        "Freight" => array("number"=>true, "minValue"=>"0.1", "maxValue"=>"1000")
    );
    // set the rules
    $grid->setValidationRules( $validation );

    // try to update the data in the table
    $grid->update($data);
} else {
    $grid->queryGrid();
}

From the code it can be seen that the method for updating is called update and it accept only one parameter - the data to be updated in key=>value definition. In is not necessary that all the field values in the data should be equal of those in the table. It is possible to update only one field.

Important

Please note that all the PHP and JS code above is automatically generated when a Render class is used.

Deleting

Deleting of record(s) in principal is equal to the insert and update, but differs form the number of accepted parameters and the input data parameter too.

The definition of the method is as follow:

public function delete(array $data, $where='', $params=array() )
{
    ...
}

The first parameter $data should contain only one key - the field (usually primary key) which will be used in generated where clause to delete the record. The value of the key can be comma separated to delete multiple records

This mean that if in the data we have 

// Create the jqGrid Edit instance
$grid = new Guriddo\jqGrid\Edit\Edit($conn, 'pdo');
...

$dataToDelete = array("RowId"=>"1, 20, 30");
$grid->delete( $dataToDelete );

will delete the records with RowId = 1, 20 and 30.

When set the second and/or third parameters the script uses only these parameters and ignores the data parameter.

To demonstrate this look at the below example:

// Create the jqGrid Edit instance
$grid = new Guriddo\jqGrid\Edit\Edit($conn, 'pdo');

$myWhere = "Mykey > ? AND MyKey < ?";
$myParams = array( 10, 50);
$grid->delete( array(),  $myWhere, $myParams);

The above code generates following SQL:

DELETE FROM myTable WHERE Mykey > 10 AND MyKey < 50;

It is good to use parameters in the query, since these are prepared to protect from SQL injection.

Important

Please note that all the PHP and JS code above is automatically generated when a Render class is used.

Custom Actions within CRUD

There are situations where is needed to perform actions (SQL queryes) before and after to do a CRUD in the database. In the Edit class this is possible before and after the action (insert, update, delete) with a help of the two methods: setBeforeCrudAction and setAfterCrudAction

The parameters of these are as follow:

public function setBeforeCrudAction($oper, $sql, $params = null)
{
   ...
}

and 

public function setAfterCrudAction($oper, $sql, $params = null)
{
   ...
}

Methods run a sql command(s) before and/or after the operation from the CRUD. Unlimited number of actions can be set.

The first parameter $oper defines the operation level - can be add, edit, del, the second parameter $sql defines the SQL commenad to be executed and the last parameter $params set the parameters passed to the SQL scrip in case they are defined.

The following code updates the name of the customer adding "-U" after updating the record at all.

namespace Guriddo\jqGrid;
use Guriddo\Utils\Utils;
...
$cid = Utils::GetParam('CustomerID');
// This command is executed immediatley after edit occur.
$grid->setAfterCrudAction('edit', 
    "UPDATE customers SET CompanyName = CONCAT(CompanyName,' -U') WHERE CustomerID=?",
array($cid));
...

Note

Please note that in case if the transactions are enabled (default) all the operations will fail, if one of the custom action(s) fail.
In case of disabled transactions only the "bad" action will fail, while the other operations will be executed.

All in One

Knowing how to Insert, Update and Delete data, it is very natural to have a method that combines all of these operations. The method is named editGrid and combines insert, update, delete and queryGrid methods in one place.

The method has the following definition (for more detailed info see api docs)

public function editGrid(array $summary=null, array $params=null, $oper=false, $echo = true)
{
    ...
}

where the first parameter $summary defines which columns should be summarized, the second parameter $params set additional parameters that can be passed to the query, $oper if set (see the possible values in $GridParams array) the operation ( add, edit, del ) is performed without to check the parameter passed to the grid via the javascript and the last parameter $echo if set to true echo the result.

The method uses prmNames names grid options to identify in which mode we are and and do the appropriate operation.

Another feature of the method is that the primary key can not be set during the definition of the script. In case if the primary key is not set, the method try to get the primary key of the table. If the primary key can not be obtained a error message is displayed and the CRUD operation is aborted.

With the following PHP code we define all the CRUD actions in the grid (insert, update, delete) without to specify the particular one (used is the HTML definition of the Updating example).

<?php
require_once 'path_to_your_project_vendor_dir/autoload.php';

use Guriddo\Utils\Utils;

// Connection to the server
$conn = new PDO('mysql:host=localhost;dbname=northwind','user','password');
// Tell the db that we use utf-8
$conn->query("SET NAMES utf8");

// Create the jqGrid Edit instance
$grid = new Guriddo\jqGrid\Edit\Edit($conn, 'pdo');
// Write the SQL Query
$grid->SelectCommand = 'SELECT OrderID, OrderDate, CustomerID, Freight, ShipName FROM orders';
$grid->dataType = "json";
$grid->table = 'orders';
$grid->setPrimaryKeyId('OrderID');
$grid->editGrid();