There are situations where the query should be diffrent depending on different conditions. jqGrid has some features which can be used.
In order to do that we can

  • Use Read query from XML file.
  • Use SelectCommant
  • Use table

Related methods

exportToExcel
queryGrid
editGrid
renderGrid

The ralated public variables

$readFromXML
$SelectCommand
$table

The logic in which the methods read the query is:
First we look if the $readFromXML variable is set to true. If the variable is true we read the query from the XML file (see below).
If the $readFromXML is false we look at $SelectCommand.
If the $SelectCommand is empty at end we look for $table

Read query from XML

In order to read the query fron XML we should use two variables - $readFromXML and $SelectCommand. The $readFromXML should be set to true and the $SelectCommand should meet some conditions. These are as follow:

SelectCommand should be set as xmlfile.sqlid. The xmlfile is the name of xml file where we store the sql commands, sqlid is the id of the required sql.

Note The xmlfile setted in the SelectCommand should not end with xml, but the same file should have xml extension

The xml file should have the following structure

 < ?xml version="1.0" encoding="UTF-8"?>
 <queries>
     <sql Id="getUserById">
        Select ..  From ..  Where ...
    </sql>
    <sql Id="validateUser">
        Select   From  Where 
    </sql>
     ...
 </queries>

Let suppose that we want not to display the Freight field (Using our example) if the user belogs to a group greater than 3.

Create a file groupright.xml which should look like this:

< ?xml version="1.0" encoding="UTF-8"?>
 <queries>
     <sql Id="allfields">
        SELECT OrderID, OrderDate, CustomerID, Freight, ShipName FROM orders
    </sql>
    <sql Id="limitedfields">
        SELECT OrderID, OrderDate, CustomerID, ShipName FROM orders
    </sql>
     ...
 </queries>

Save the file in certain location and write the following PHP script

<?php
require_once 'jq-config.php';
// include the jqGrid Class
require_once "php/jqGrid.php";
// include the PDO driver class
require_once "php/jqGridPdo.php";
// Connection to the server
$conn = new PDO(DB_DSN,DB_USER,DB_PASSWORD);

$group = $_SESSION['usergroup'];

// Create the jqGrid instance
$grid = new jqGrid($conn);
// Enable reading from XML
$grid->readFromXML = true;

// Set the query based on the condition 
if($group <= 3)
    $grid->SelectCommand = 'pathtothefile/groupright.allfields';
else 
    $grid->SelectCommand = 'pathtothefile/groupright.limitedfields';

$grid->dataType = "json";
$grid->queryGrid();
?>