Using jqGrid Control with PHP
by Vincy. Last modified on July 13th, 2022.
jqGrid is very popular and used to manage data with the grid user interface. It is provided with the facility of handling dynamic data loaded into the grid. It uses the 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 such as SQL, MySQL, Oracle and etc. We can integrate Twitter bootstrap and jQuery UI Themeroller to view grid UI with a 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, but jqGrid also 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 the latest jQuery libraries and also delivered with advanced features, optimizations, bug fixing and etc.
Loading Data to jqGrid from the 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='https://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="https://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 the 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.
$(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: ""
});
});
This script sends AJAX requests with required parameters to the PHP page, getGridData.php. The following code shows a PHP script called.
<?php
$conn = mysqli_connect("localhost", "root", "test", "blog_samples") or die("Connection Error: " . mysqli_error($conn));
$page = $_GET['page'];
$limit = $_GET['rows'];
$sidx = $_GET['sidx'];
$sord = $_GET['sord'];
if (! $sidx)
$sidx = 1;
$result = mysqli_query($conn, "SELECT COUNT(*) AS count FROM users");
$row = mysqli_fetch_array($result);
$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 = mysqli_query($conn, $SQL) or die("Couldn't execute query." . mysqli_error($conn));
$i = 0;
while ($row = mysqli_fetch_array($result)) {
$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 the database table in the form of a PHP array. This array data will be encoded as an JSON data using PHP built-in function json_encode(). Previously, we have seen how to encode-decode JSON with PHP. Also. we have seen a comprehensive article on JSON handling with PHP.
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.
Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.
Comments to “Using jqGrid Control with PHP”
Leave a Reply
Hi, I'm Vincy. I help build websites, grow businesses, big and small. If that is what you want, contact me.
I'm currently available for freelance work.
Hello, Lets say this is a web application and we have number of jsps. In that condition where are you keeping your php file. Next to jsp file or where?
Hi, this example it is using an array but using a database query?
Regards
Hi Luis,
The JSON object array is formed by using database result only.
If you want something else, enter the code snippet or algorithm for that.
$page = $_GET[‘page’];
$limit = $_GET[‘rows’];
$sidx = $_GET[‘sidx’];
$sord = $_GET[‘sord’];
Hi, here what is meant and what is purpose of this code because I can’t understand these code,what are the purpose of these variables????
So please may you explain briefly
This is to get and display the pagination.