Pagination, Previous, Next, First and Last button using jQGrid, PHP, MySQL, jQuery and Ajax

Posted 6:26 pm by muni & filed under Ajax, HTML5, JQUERY, MYSQL, PHP.

Web applications realiablity depends on how much user friendliness that application was to the user. Mostly when we display large pile of data to user, we need to give choices to the user how they want to view those data’s that greatly increases realiablity of appliaction. In this tutorial we are going to see how to display large pile data to user with pagination, previous, next , first and last button functionality using jQGrid, PHP, MySQL, jQuery and Ajax.

This is very very simple to tutorial, all you need to do is just write two lines SQL query. One for to find counts (i.e. count query)

SELECT COUNT(*) AS count FROM employees

Another query is to fetch data’s

SELECT * from employees

jQGrid PHP Tutorial

Step 1:

First create sample ‘employees’ table using following SQL query.

CREATE TABLE IF NOT EXISTS `employees` (
  `EmployeeID` int(11) NOT NULL AUTO_INCREMENT,
  `LastName` varchar(20) NOT NULL,
  `FirstName` varchar(10) NOT NULL,
  `Title` varchar(30) DEFAULT NULL,
  `TitleOfCourtesy` varchar(25) DEFAULT NULL,
  `BirthDate` datetime DEFAULT NULL,
  `HireDate` datetime DEFAULT NULL,
  `Address` varchar(60) DEFAULT NULL,
  `City` varchar(15) DEFAULT NULL,
  `Region` varchar(15) DEFAULT NULL,
  `PostalCode` varchar(10) DEFAULT NULL,
  `Country` varchar(15) DEFAULT NULL,
  `HomePhone` varchar(24) DEFAULT NULL,
  `Extension` varchar(4) DEFAULT NULL,
  `Photo` varchar(255) DEFAULT NULL,
  `Notes` text,
  `ReportsTo` int(11) DEFAULT NULL,
  PRIMARY KEY (`EmployeeID`),
  KEY `LastName` (`LastName`),
  KEY `PostalCode` (`PostalCode`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=214 ;

Step 2:

Now insert some sample data into the table using following sample data’s.

INSERT INTO `employees` (`EmployeeID`, `LastName`, `FirstName`, `Title`, `TitleOfCourtesy`, `BirthDate`, `HireDate`, `Address`, `City`, `Region`, `PostalCode`, `Country`, `HomePhone`, `Extension`, `Photo`, `Notes`, `ReportsTo`) VALUES
(NULL, ‘Davolio’, ‘Dev’, ‘Sales Representative’, ‘Ms.’, ’1968-12-08 00:00:00′, ’1992-05-01 00:00:00′, ’507 – 20th Ave. E.Apt. 2A’, ‘Seattle’, ‘WA’, ’98122′, ‘USA’, ‘(206) 555-9857′, ’5467′, ‘EmpID1.bmp’, ‘Education includes a BA in psychology from Colorado State University.  She also completed “The Art of the Cold Call.”  Nancy is a member of Toastmasters International.’, 2),
(NULL, ‘Leverling’, ‘sam’, ‘Sales Representative’, ‘Ms.’, ’1963-08-30 00:00:00′, ’1992-04-01 00:00:00′, ’722 Moss Bay Blvd.’, ‘Kirkland’, ‘WA’, ’98033′, ‘USA’, ‘(206) 555-3412′, ’3355′, ‘EmpID3.bmp’, ‘Janet has a BS degree in chemistry from Boston College).  She has also completed a certificate program in food retailing management.  Janet was hired as a sales associate and was promoted to sales representative.’, 2),
(NULL, ‘Peacock’, ‘Margaret’, ‘Sales Representative’, ‘Mrs.’, ’1958-09-09 00:00:00′, ’1993-05-03 00:00:00′, ’4110 Old Redmond Rd.’, ‘Redmond’, ‘WA’, ’98052′, ‘USA’, ‘(206) 555-8122′, ’5176′, ‘EmpID4.bmp’, ‘Margaret holds a BA in English literature from Concordia College and an MA from the American Institute of Culinary Arts. She was temporarily assigned to the London office before returning to her permanent post in Seattle.’, 2),
(NULL, ‘Buchanan’, ‘Steven’, ‘Sales Manager’, ‘Mr.’, ’1955-03-01 00:00:00′, ’1993-10-17 00:00:00′, ’14 Garrett Hill’, ‘London’, NULL, ‘SW1 8JR’, ‘UK’, ‘(71) 555-4848′, ’3453′, ‘EmpID5.bmp’, ‘Steven Buchanan graduated from St. Andrews University, Scotland, with a BSC degree.  Upon joining the company as a sales representative, he spent 6 months in an orientation program at the Seattle office and then returned to his permanent post in London, where he was promoted to sales manager.  Mr. Buchanan has completed the courses “Successful Telemarketing” and “International Sales Management.”  He is fluent in French.’, 2),
(NULL, ‘King’, ‘Robert’, ‘Sales Representative’, ‘Mr.’, ’1960-05-29 00:00:00′, ’1994-01-02 00:00:00′, ‘Edgeham Hollow\r\nWinchester Way’, ‘London’, NULL, ‘RG1 9SP’, ‘UK’, ‘(71) 555-5598′, ’465′, ‘EmpID7.bmp’, ‘Robert King served in the Peace Corps and traveled extensively before completing his degree in English at the University of Michigan and then joining the company.  After completing a course entitled “Selling in Europe,” he was transferred to the London office.’, 5),
(NULL, ‘Callahan’, ‘Laura’, ‘Inside Sales Coordinator’, ‘Ms.’, ’1958-01-09 00:00:00′, ’1994-03-05 00:00:00′, ’4726 – 11th Ave. N.E.’, ‘Seattle’, ‘WA’, ’98105′, ‘USA’, ‘(206) 555-1189′, ’2344′, ‘EmpID8.bmp’, ‘Laura received a BA in psychology from the University of Washington.  She has also completed a course in business French.  She reads and writes French.’, 2),
(NULL, ‘Dodsworth’, ‘Anne’, ‘Sales Representative’, ‘Ms.’, ’1969-07-02 00:00:00′, ’1994-11-15 00:00:00′, ’7 Houndstooth Rd.’, ‘London’, NULL, ‘WG2 7LT’, ‘UK’, ‘(71) 555-4444′, ’452′, ‘EmpID9.bmp’, ‘Anne has a BA degree in English from St. Lawrence College.  She is fluent in French and German.’, 5),
(NULL, ‘ayothi’, ‘muni’, ‘smart’, ‘good’, ’2013-06-19 00:00:00′, ’2013-06-19 00:00:00′, ‘madhanur’, ‘krishnagiri’, ‘tamil nadu’, ’635123′, ‘India’, ’9942865203′, NULL, NULL, ‘thanks’, 5)

Step 3:

Now create config.php file and write database connections in it, That helps you to keep data connections in one file.

<?php
define('DB_HOST', 'localhost');
define('DB_NAME', 'test');
define('DB_USER','root');
define('DB_PASSWORD','');

$con=mysql_connect(DB_HOST,DB_USER,DB_PASSWORD) or die("Failed to connect to MySQL: " . mysql_error());
$db=mysql_select_db(DB_NAME,$con) or die("Failed to connect to MySQL: " . mysql_error());

?>

Step 4:

Now create index.php file and add following html source code in it.

<!DOCTYPE HTML>
<html>
<head>
<title>jqGrid php tutorial</title>
</head>
<body>
	<table id="list2"></table> 
	<div id="pager2" ></div>	
</body>
</html>

Step 5:

Now add following css and js files in the head section of index.php file.

<link rel='stylesheet' href='css/jquery-ui-custom.css'/>
<link rel='stylesheet' href='css/ui.jqgrid.css'/>
<script src='js/jquery-1.9.0.min.js'></script>
<script src='js/grid.locale-en.js'></script>
<script src='js/jquery.jqGrid.min.js'></script>

Step 6:

Create server.php file and add following php scripts in it. We will call this file through ajax to get data from the database.

Don’t modify anything in this below code, all need to change following two lines SQL query.

$result = mysql_query("SELECT COUNT(*) AS count FROM employees");

and this

$SQL = "SELECT * from employees ORDER BY $sidx $sord LIMIT $start , $limit";

<?php
 error_reporting(0);
 require_once 'config.php';

 $page = $_GET['page']; // get the requested page
 $limit = $_GET['rows']; // get how many rows we want to have into the grid
 $sidx = $_GET['sidx']; // get index row - i.e. user click to sort
 $sord = $_GET['sord']; // get the direction
 if(!$sidx) $sidx =1; // connect to the database
 $result = mysql_query("SELECT COUNT(*) AS count FROM employees");
 $row = mysql_fetch_array($result,MYSQL_ASSOC);
 $count = $row['count'];

 if( $count >0 ) { 
 $total_pages = ceil($count/$limit);
 //$total_pages = ceil($count/1);
 } else {
 $total_pages = 0; 
 } if ($page > $total_pages) 
 $page=$total_pages; 
 $start = $limit*$page - $limit; // do not put $limit*($page - 1) 
 $SQL = "SELECT * from employees ORDER BY $sidx $sord LIMIT $start , $limit"; 
 $result = mysql_query( $SQL ) or die("Couldn t execute query.".mysql_error());
 $responce->page = $page;
 $responce->total = $total_pages;
 $responce->records = $count; 
 $i=0;
 while($row = mysql_fetch_array($result,MYSQL_ASSOC)) { 
 $responce->rows[$i]['id']=$row[EmployeeID];
 $responce->rows[$i]['cell']=array($row['EmployeeID'],$row['LastName'],$row[FirstName],$row[BirthDate],$row[Address],$row['City'],$row['Region'],$row['Country'],""); $i++;
 } 
 echo json_encode($responce);

?>

Step 6:

Finally add the following jQuery script in the index.php file.

<script>
	   jQuery("#list2").jqGrid({ 
	   url:'server.php?q=2', 
	   datatype: "json",
	   colNames:['EMPLOYEE ID','LAST NAME', 'FIRST NAME', 'BIRTH DATE','ADDRESS','CITY','REGION','COUNTRY'], 
	   colModel:[ {name:'EmployeeID',index:'EmployeeID', width:30,classes: 'cvteste'}, 
	   {name:'LastName',index:'LastName', width:90,classes: 'cvteste'}, 
	   {name:'FirstName',index:'FirstName', width:80,classes: 'cvteste'},
	   {name:'BirthDate',index:'BirthDate', width:135,align:"center",classes: 'cvteste'},
       {name:'Address',index:'Address', width:150, sortable:false,classes: 'cvteste'},
	   {name:'City',index:'City', width:60, sortable:false,classes: 'cvteste'},
	   {name:'Region',index:'Region', width:70, sortable:false,classes: 'cvteste'},
	   {name:'Country',index:'Country', width:70, sortable:false,classes: 'cvteste'}	   
	   ],
	   rowNum:10, 
	   rowList:[10,20,30], 
	   pager: '#pager2', 
	   sortname: 'EmployeeID', 
	   recordpos: 'left', 
	   viewrecords: true, 
	   sortorder: "asc", 
	   height: '100%'
	   });

	</script>

where number parameters in the colNames and colModel must be equal..