Using jqGrid Control with PHP

jqGrid is very popular and used to manage data with grid user interface. It is provided with the facility of handling dynamic data loaded into the grid. It uses jQuery Javascript library for the grid control operations handled by the use of AJAX calls.

It will be integrated with any server-side technologies like PHP, ASP.NET and etc. And, it supports most of the popular databases as such as SQL, MySQL, Oracle and etc. We can integrate twitter bootstrap and jQuery UI Themeroller to view grid UI with good look and feel.

With the support of customized jQuery library functions and AJAX calls, integrating jqGrid for our PHP projects will increase its speed, efficiency and etc. For example, we have seen our own PHP CRUD script a few days before. By using the jqGrid plugin, we can simply create a project with CRUD functionalities with less time and code, comparatively. Not only CRUD, jqGrid provides various features like pagination, sorting records with column order, data mapping, searching and etc.
jqGrid is developed at Triand. For each new version, it is upgraded with latest jQuery libraries and also delivered with advanced features, optimizations, bug fixing and etc.

Loading Data to jqGrid from PHP script

Let us have a simple example to load PHP data for grid UI shown using jqGrid. For that, we need to follow the steps listed below.

• Include jQuery and jqGrid library files.
• AJAX call to get data from a PHP page.
• Load AJAX response text with jqGrid UI.

Include jQuery and jqGrid Library Files.

To use  jqGrid for our PHP script, we need to include the following CSS and Javascript files shown in the following code block.

<link rel='stylesheet' type='text/css' href='http://code.jquery.com/ui/1.10.3/themes/redmond/jquery-ui.css' />
<link rel='stylesheet' type='text/css' href='http://www.trirand.com/blog/jqgrid/themes/ui.jqgrid.css' />

<script src="http://code.jquery.com/jquery-1.10.1.min.js"></script>
<script type='text/javascript' src='http://www.trirand.com/blog/jqgrid/js/jquery-ui-custom.min.js'></script>        
<script type='text/javascript' src='http://www.trirand.com/blog/jqgrid/js/i18n/grid.locale-en.js'></script>
<script type='text/javascript' src='http://www.trirand.com/blog/jqgrid/js/jquery.jqGrid.js'></script>

Instead of using official website URL, we can make a local copy of these files and specify their path accordingly.

AJAX call for Getting Data from PHP.

For raising an AJAX request to get PHP data as a response text, we can use the following custom Javascript code.

<script>
$(document).ready(function () {
$("#list_records").jqGrid({
url: "getGridData.php",
datatype: "json",
mtype: "GET",
colNames: ["User Id", "User Name", "First Name", "Last Name"],
colModel: [
{ name: "userId",align:"right"},
{ name: "userName"},
{ name: "firstName"},
{ name: "lastName"}
],
pager: "#perpage",
rowNum: 10,
rowList: [10,20],
sortname: "userId",
sortorder: "asc",
height: 'auto',
viewrecords: true,
gridview: true,
caption: ""
});  
});
</script>

This script sends AJAX request with required parameters to the PHP page, getGridData.php. The following code shows PHP script called.

<?php 
$conn = mysql_connect("localhost", "root", "") or die("Connection Error: " . mysql_error()); 
mysql_select_db("phppot_examples") or die("Error connecting to db."); 

$page = $_GET['page']; 
$limit = $_GET['rows']; 
$sidx = $_GET['sidx']; 
$sord = $_GET['sord']; 

if(!$sidx) $sidx =1; 

$result = mysql_query("SELECT COUNT(*) AS count FROM users"); 
$row = mysql_fetch_array($result,MYSQL_ASSOC); 

$count = $row['count']; 
if( $count > 0 && $limit > 0) { 
$total_pages = ceil($count/$limit); 
} else { 
$total_pages = 0; 
} 
if ($page > $total_pages) $page=$total_pages;
$start = $limit*$page - $limit;
if($start <0) $start = 0; 

$SQL = "SELECT * FROM users ORDER BY $sidx $sord LIMIT $start , $limit"; 
$result = mysql_query( $SQL ) or die("Couldn't execute query.".mysql_error()); 

$i=0;
while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
$responce->rows[$i]['id']=$row['userId'];
$responce->rows[$i]['cell']=array($row['userId'],$row['userName'],$row['firstName'],$row['lastName']);
$i++;
}
echo json_encode($responce);
?>

This PHP script will get user records from database table in the form of PHP array. This array data will be encoded as an JSON data using PHP json_encode() function. By printing this data in AJAX called PHP page, it will be sent as the response text of the AJAX request generated from a Javascript function. This data will be parsed and loaded into jqGrid. After that, the user records will be seen with the jqGrid UI as shown as below.

Download