Please support us by disabling ad blocker for smarttutorials.net ...

jQGrid PHP inline Editing

Posted by & filed under Ajax, CSS, HTML5, JQUERY, MYSQL, PHP.

In this tutorial we are going to see how to implement inline edit of jQGrid using php. This jQGrid table has extra column, which has three button to perform edit, save and cancel actions respectively. when user clicks on particular row edit button the whole fields on the row are converted into editable text field, once they finish edit and clicks on save button all the data’s will be updated in the database table through ajax. I am explaining all the functions very detailed manner, just follow this tutorial step by step.

Before continuing this tutorial please refer my previous tutorial (basic tutorial on this topic) Pagination, Previous, Next, First and Last button using jQGrid, PHP, MySQL, jQuery and Ajax

 

 

jQGrid PHP inline Editing Tutorial

jQGrid PHP inline Editing Tutorial

 

To implement the above functionality you saw in the demo, all you need to do is just write two simple SQL query and 10 lines of php script to update editable data’s.

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 inline Editing Tutorial</title>
</head>
<body>
	<table id="rowed2"></table> 
	<div id="prowed2" ></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:

Add the following jQuery script in the index.php file.

<script>
	jQuery("#rowed2").jqGrid({
   	url:'server.php?q=3',
	datatype: "json",
   	 colNames:['EMPLOYEE ID','LAST NAME', 'FIRST NAME', 'BIRTH DATE','ADDRESS','CITY','REGION','COUNTRY','ACTIONS'], 
	   colModel:[ 
	   {name:'EmployeeID',index:'EmployeeID', width:30,classes: 'cvteste'}, 
	   {name:'LastName',index:'LastName', width:90,classes: 'cvteste',editable:true}, 
	   {name:'FirstName',index:'FirstName', width:80,classes: 'cvteste',editable:true},
	   {name:'BirthDate',index:'BirthDate', width:150,align:"center",classes: 'cvteste',editable:true},
       {name:'Address',index:'Address', width:140, sortable:false,classes: 'cvteste',editable:true},
	   {name:'City',index:'City', width:80, sortable:false,classes: 'cvteste',editable:true},
	   {name:'Region',index:'Region', width:70, sortable:false,classes: 'cvteste',editable:true},
	   {name:'Country',index:'Country', width:70, sortable:false,classes: 'cvteste',editable:true},
       {name:'act',index:'act', width:130,sortable:false}	   
	   ],
   	rowNum:10,
   	rowList:[10,20,30],
   	pager: '#prowed2',
   	sortname: 'EmployeeID',
    viewrecords: true,
	height:'100%',
    sortorder: "asc",
	gridComplete: function(){
		var ids = jQuery("#rowed2").jqGrid('getDataIDs');
		for(var i=0;i<ids.length;i++){
			var cl = ids[i];
			be = "<input style='height:22px;width:40px;' type='button' value='Edit' onclick=\"jQuery('#rowed2').jqGrid('editRow','"+cl+"');\"  />"; 
			se = "<input style='height:22px;width:40px;' type='button' value='Save' onclick=\"jQuery('#rowed2').jqGrid('saveRow','"+cl+"');\"  />"; 
			ce = "<input style='height:22px;width:50px;' type='button' value='Cancel' onclick=\"jQuery('#rowed2').jqGrid('restoreRow','"+cl+"');\" />"; 
			jQuery("#rowed2").jqGrid('setRowData',ids[i],{act:be+se+ce});
		}	
	},
	editurl: "update.php"
});
jQuery("#rowed2").jqGrid('navGrid',"#prowed2",{edit:false,add:false,del:false});   
	</script>

where editable:true script turns each field as an editable one, when clicks on edit button.

Step 7:

Finally create update.php file, which gets data from jQGrid table row through ajax when user clicks on save button on any particular row of the table.

<?php
require_once 'config.php'; 

 $id = $_POST['id'];
  //This contains the data that will update the db
 $LastName = $_POST['LastName'];
 $FirstName = $_POST['FirstName'];
 $BirthDate = $_POST['BirthDate'];
 $Address = $_POST['Address'];
 $City = $_POST['City'];
 $Region = $_POST['Region'];
 $Country = $_POST['Country'];

 mysql_query("UPDATE employees SET LastName='$LastName',FirstName='$FirstName',BirthDate='$BirthDate',Address='$Address',City='$City',Region='$Region',Country='$Country' where EmployeeID=$id")
 or die(mysql_error());
 mysql_close($db);

?>

 .

Download Premium Only Scripts & 80+ Demo scripts Instantly at just 1.95 USD per month + 10% discount to all Exclusive Scripts

If you want any of my script need to be customized according to your business requirement,

Please feel free to contact me [at] muni[at]smarttutorials.net

Note: But it will be charged based on your customization requirement

Get Updates, Scripts & Other Useful Resources to your Email

Join 10,000+ Happy Subscribers on feedburner. Click to Subscribe (We don't send spam)
Every Email Subsciber could have access to download 100+ demo scripts & all future scripts.

  • CREATE TRIGGER testTrigger BEFORE INSERT ON kidz_accesslogs
    FOR EACH ROW BEGIN
    SET NEW.media_id = REPLACE(SUBSTRING_INDEX(NEW.xsuriquery, ‘&’, 1), ‘vid=’, ”);
    SET NEW.user_id = REPLACE(SUBSTRING_INDEX(NEW.xsuriquery, ‘&’, -1), ‘uid=’, ”);
    END;

  • SHOW TRIGGERS LIKE ‘%’;
    DROP TRIGGER testTrigger

    CREATE TRIGGER testTrigger1 AFTER INSERT ON kidz_accesslogs
    FOR EACH ROW BEGIN
    UPDATE kidz_media SET no_of_views = (no_of_views+1) WHERE id= NEW.media_id and NEW.xevent==’destroy’;
    END

  • mohit

    Im very new to jqgrid. I have seen your jqgrid demo this is awesome ………
    The only thing that i need is search/find option.In ur demo that option didn’t work.
    So kindly help me ……….

  • muni

    Hi Mohit,

    I hadded search option in my new tutorial. please refer the following tutorial

    http://www.smarttutorials.net/instant-search-with-pagination-in-php-mysql-jquery-and-ajax/

  • Abhishek Asthana

    Hi Muni,

    greetings dear,
    A very good tutorial,and for a newbe like me its very nice.there is one issue,like if we want that a column should be hidden from viewing then what can be done?what is the procedure.Because like there is a user_id by which the data will get updated.So what can I do?Please help

  • Alessandro Nestola

    Following the example I can not pass parameters page or limits for reading and id for the upgrade and so on.
    how can I do?

  • Damon

    Hi Muni,
    Is it possible to put the server.php & update.php code into a functions.php and call it from the jquery code? I’d like to use the table grid for multiple views without having a query/update file for each table. It would be nice to have those sql calls in a functions file. I’ve tried the following but the data isn’t returning.

    #table1_view being the function name, I don’t get any errors but I also don’t get any data returned.
    url:’inc/functions.php/table1_view’,
    datatype: “json”,

    Thanks for you time.

  • Damon

    Did you ever figure this out, I’ve copied the exact code and can’t get data to display, the grid layout does just no data.

  • BennyHoang

    Hi Muni !
    Its so awesome tutorial. Many thanks you guy.
    All the best, my searching tool running good in php 5 and apache 2. but in php4 can not work.
    Would u plsz show me how to fix it. Thanks.

  • BennyHoang

    Hi Asthana !
    I had the same issue like u, and u should set all attributes “editable” in all shown collumns. Updating data just in file update.php.

  • Rajiv Kumar

    my save button can’t save data to the database

  • mark empeynado

    the saving to the database is not working in my database, but your sample codes and database are doing fine

  • sandeep dhaliwal

    i have tried the above code but my data is not fetched i.e my grid is empty ??

  • sandeep dhaliwal

    ?Please help

Get Instant Script Download Access!