Saturday, October 9th, 2010

Inline edit using Ajax in Jquery

Requirements:

This tutorial requires core javascript knowledge and basic knowledge about jquery.

What is Ajax ?

AJAX is a technology that kept evolving from java applet days. It was notably used in web development when Google started deploying it in Gmail and Google maps extensively.

AJAX stands for Asynchronous Javascript and XML. As the name suggests the request from the end user is made asynchronously to the server. The requests and responses for the ajax is in XML format.

A request that is sent asynchronously from a browser to the server and receives the response in an async mode, doesn’t require you to refresh the page to display the results on the page. In other words, the user doesn’t realize when the request is been sent and the response is received back.

Now let us see an example of ajax using Jquery.

Ajax in Jquery

Jquery provides a very powerful library for ajax operations. As part of this tutorial, we will see how to edit the contents of the table and store that in the database using php and jquery. Remember we will be performing the inline edits asynchronously.

Step 1:

Let us start with building a database. Create a test database and load some test data.

Next we need to connect to database from our php code. Let me call this php file config.php and make sure you don’t give public access to it, as you will be storing all your DB credentials in this file.

define('DBHOST','YOUR HOST');
define('DBUSER','USERNAME');
define('DBPASS','PASSWORD');
define('DBNAME','DBNAME');
/*code to follow*/

Step 2:

Now you can test your database connection and write a function to retrieve the contents from the test db you created in step1.

$conn = mysql_connect(DBHOST,DBUSER,DBPASS);
mysql_select_db(DBNAME,$conn);

function get_data()
{
	
	$sql = "select * from TABLENAME";
	
	$rs = mysql_query($sql);
	
	return $rs;
}

Step 3:

Let us build the HTML to display the contents in a table we just retrieved from db in step 2. I marked the class of the td element that I want to edit to say ‘edit’ followed by its tag and row number in the db for updating the respective record in db.

No Name Description Price
'.$rows['id'].' '.$rows['name'].' '.$rows['description'].' '.$rows['price'].'

Step 4:

Now the table can be styled.

body
{
	margin:0;
	padding:0;
	background:#f4f4f4;
}

h1{margin:80px auto 0;width:288px}

table
{
	margin:100px auto;
	width:500px;
	border:1px solid #646464;
	position:relative;
}

tr,td
{
	text-align:center;
	border: 1px solid #ccc;
}

tr
{
	background:#99F;
}

.heading{background:#ccc;}

.alt
{
	background:#C4C4FF;
}

Step 5:

So now we have come to the javascript portion of the tutorial that adds ajax to your table you built. I want to make the td element editable when a ‘click’ event is triggered on it. The javascript that is used for making it an editable item is below,

$('td.edit').click(function(){
$(this).addClass('ajax');
$(this).html('');										
$('#editbox').focus();
});

Step 6:

We also want the editable items to be switched around as we move clicking on other td elements. So I added a script that switches the ajax class among the editable elements. We can add this script to the click event function. Now the above code looks like this,

$('td.edit').click(function(){
							 
 $('.ajax').html($('.ajax input').val());
 $('.ajax').removeClass('ajax');

 $(this).addClass('ajax');
 $(this).html('');
										
$('#editbox').focus();								        
  
} );

Step 7:

Next we want the edits made to be passed on to the server to store it in DB. I want that to happen when user presses Enter key. How can that be achieved? First we have to check if a user has pressed Enter key and next if it is Enter key pressed on that particular editable element, we have to pass the value entered by the user to our server side code to handle the data storage.

$('td.edit').keydown(function(event){
    if(event.which == 13)
   { 
      $.ajax({    type: "POST",
      url:"config.php",
      data: //To be discussed in next step,
      success: function(data){
	   $('.ajax').html($('.ajax input').val());
	    $('.ajax').removeClass('ajax');
       }});
    }
								  
});

The above javascript tells the browser to look for the keydown event and if it is triggered it will check if the keypressed is ‘Enter key’ ( Enter key value ’13′). Once it passes the check it calls the ‘config.php’ (server-side code). Note it uses the POST method for the call.

On a successful callback the success function gets executed. I want to make the editable area back to the regular td element. So we will be again removing the ‘ajax’ class for that td element and pass the value of the editable box to the td element.

Step 8:

In the above step you would have noticed ‘data’ key value pair. We will discuss how we are going to construct the data that will be sent as request to the config.php .

In Step 3, I have mentioned that editable td elements will be marked with their corresponding tag (table column name) and row number of the table. We are going to use that to pass as data to server-side along with the edited value.

so now the above code looks like this with complete data construction.

$('td.edit').keydown(function(event){
	 arr = $(this).attr('class').split( " " );
	 if(event.which == 13)
	 { 
		$.ajax({    type: "POST",
			        url:"config.php",
				data: "value="+$('.ajax input').val()+
                                        "&rownum="+arr[2]+"&field="+arr[1],
				success: function(data){
					 $('.ajax').html($('.ajax input').val());
				         $('.ajax').removeClass('ajax');
			}});
		}
								  
	});

Step 9:

We have to write the function to handle the data that have been sent in the above step. Also this function will be called only if the data have been received from the browser.

//check if the data is received from the browser
//and calls the update function
if(isset($_POST['rownum']))
{
	update_data($_POST['field'],$_POST['value'],$_POST['rownum']);
}
/*php code*/
function update_data($field, $data, $rownum)
{

	
	$sql = "update TABLENAME set ".$field." = '".$data."' where id = ".$rownum;
	
	mysql_query($sql) or die("Couldn't connect to db");
	
	
}

So now your config.php should looks like this

define('DBHOST','YOUR HOST');
define('DBUSER','USERNAME');
define('DBPASS','PASSWORD');
define('DBNAME','DBNAME');

$conn = mysql_connect(DBHOST,DBUSER,DBPASS);
	
mysql_select_db(DBNAME,$conn);

if(isset($_POST['rownum']))
{
	update_data($_POST['field'],$_POST['value'],$_POST['rownum']);
}


function get_data()
{
	
	$sql = "select * from TABLENAME";
	
	$rs = mysql_query($sql);
	
	return $rs;
}

function update_data($field, $data, $rownum)
{

	
	$sql = "update TABLENAME set ".$field." = '".$data."' where id = ".$rownum;
	
	mysql_query($sql) or die("Couldn't connect to db");
	
	
}

Step 10:

Finally, I want to add the ‘blur’ event to the editable box, so that the editable box disappears when the focus is blurred out from it.

$('#editbox').live('blur',function(){
		$('.ajax').html($('.ajax input').val());
		$('.ajax').removeClass('ajax');
});

Note:
The above script uses ‘live’ keyword to bind the ‘blur’ event to the editable box, because the ‘editbox’ element is a dynamic one. It is been added dynamically when you click on any editable td element.

This completes the tutorial. Hope you enjoyed this tutorial and if you liked this tutorial consider sharing it.

Category: Javascript
You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.
  • JD

    Where is the $alt variable declared? I’m not new to php, but haven’t found it in index or config .php’s.

  • http://iwebux.com Iwebux

    The $alt variable is declared in else condition.

  • Anon

    how did you make only price editable?

  • http://iwebux.com Iwebux

    If you see in Step 3 I have mentioned ‘edit’ for class. The elements that contain edit as class name can be edited and those that don’t cannot be edited.

  • Mark

    In the class (class=”edit name ‘.$rows['id'].’”>’.$rows['name'].’) I want also out the name of the table I’m about to change.
    How do I do this?

  • http://iwebux.com Iwebux

    There are two ways of doing this. One method is to use mysql_tablename and other is to write a query the table to retrieve and use it.

    You can get more information on this here. http://php.net/manual/en/function.mysql-tablename.php

    Send us how it goes for you. I hope you have some PHP background.

  • Mark

    It was pretty easy to achieve actually. Now I can use the script with a lot of pages without copying the file for each page.

  • Mark

    I have this great script working in my normal pages although in some pages my content is displayed by this code: http://www.w3schools.com/ajax/ajax_database.asp When I use the script for inline edit it won;t work, the text is not editable, can you help me out?

  • http://iwebux.com Iwebux

    The code for the link that you have here is conventional javascript ajax method. This tutorial is about achieving the ajax post using Jquery framework. You have to either follow this tutorial for making those conventional page work for you or you have to either write a conventional script for it to work, which is out of scope of this tutorial.

  • ray

    code looks good and it seems to be working fine, the only issue i have is that is not updating the database. Also, NO. is not showing the row count.

  • http://iwebux.com Iwebux

    May I know what you are doing to update the row values to the column. The example provided here looks for keyboard enter key to submit the updates to the database. I would also suggest you to check if you have the privilege to update the database you were trying to update.

  • Raghupathi_june5

    ya it’s nice code…but my problem is value not yet updated ,it’s showing undefined …what can i do for this?

  • http://iwebux.com Iwebux

    I would suggest to check for data type you are using….looks like a datatype mismatch.

  • Joaquin Gamboa

    I have a doubt i changed the fields
    id now is rut, name now is nombres, and when i call update function the text show as changed but in db there arent changes.
    Sorry my bad english.

  • Sachin

    Thanks for the script but can you pls tell me if I want to make one field textarea, how do I do that?

  • http://iwebux.com Iwebux

    check your db connection and privileges.

  • http://iwebux.com Iwebux

    Elaborate what you want to do with textarea.

  • LexLeche

    HI, thank you for the great script. I have a question about having multiple tables on the same page. I see that the javascript allows the use for one config.php. But what if I want more than one editable table (using a different database table of course) on that same page, wouldn’t i need to create a separate config2.php file? I am having all sorts of issues just trying to get more  than one to work, and hope it’s possible…Thanks for any help you can provide.

  • Sachin

    Sorry to reply you late. I want this script to edit the description field with Button rather capturing the event. 

  • http://iwebux.com Iwebux

    There is more than way you can achieve that. It really depends on what you want to do here. One way would be just using one config.php and having two different functions serving two tables and n functions for n tables. Other approach would be just two separate ajax calls going to two different config files. Again for this approach you have to  capture events on each table you are trying to map to their corresponding config files.

    So its basically where you want to define those logic, either in the client side or server side.

  • http://iwebux.com Iwebux

    Well, you can edit and add the event on a button that you would like to place on the page and follow the same procedure to update the description. Remember for demo purpose, I have made the description not editable. So just add the appropriate ‘edit’ class to make it editable.

  • Ablbaset

    I really loving this script. It’s solve my big problem. Thank you.

  • Dave

    This is excellent – thank you. I found it a little confusing for the end user as if they do not press enter the new value seems to stick. Until you refresh. So I wanted it to only change the value in the cell if enter was pressed.

    As a work around I stored the old value first using your click function 
    eg: $(this).addClass(‘ajax’); $OLDVAL = $(this).text();
    Then in your blur event I put$(‘.ajax’).html($OLDVAL);$(‘.ajax’).removeClass(‘ajax’);

    Basically trying to make the value revert to the old if enter was not pressed.

    Bit of a hack work around, maybe you have a better or simpler way?Thanks again

  • Prabha

    Thanx a lot …

  • shanth

    For some reason I am not able to see the edited data on the mysql database. can anyone help me out with this?

  • Guest

    Hi Iwebux, I am able to edit the values in the table, thanks for that. But I am not seeing the change in mysql database. I have all the privilege to update the table though. Can you please help me out with this?

  • Josh Mountain

    Thanks for the tutorial, this works great! Is there a way to prevent it from selecting the entire field on click? As it is, if I just want to change one character from the field it isn’t possible without retyping the whole field.

  • http://www.facebook.com/candraadiputra Candra Adi Putra

    thanks… really great POST ever about inline editing…simple and easy to understand

  • poe

    This works great for one table.
    What if i have 5 mysql tables from where I populate the table in index.php?
    How can I update one of them, how can I figure out which one to update?
    Thanks!

  • Chitra

    data: “value=”+$(‘.ajax input’).val()+
    “&rownum=”+arr[2]+”&field=”+arr[1], Can you explain why you taken rownum=arr[2] and field=arr[1].I bit confused?

  • Ardian Haxha

    I had to remove $field to get it working, else it would not save the data.

  • Fred

    Please tell me what did you do? You removed in conf and index.php the $field?

  • Michael Williams

    FYI – in case someone else needs this in the near future, if you are using a newer version of JQuery or using Bootstrap, you need to update step 10…live.() is deprecated, so the code below worked for me…

    $(document).on(‘blur’,'#editbox’, function(){
    $(‘.ajax’).html($(‘.ajax input’).val());
    $(‘.ajax’).removeClass(‘ajax’);
    });

  • Dan Abbate

    I just can’t seem to get this to save back to MySql. Everything seems to work perfectly. I can change the data on the table (when hitting Enter) but it just won’t save. There are no errors. Has anybody that has had this issue found a solution? Any help would be greatly appreciated.

  • http://e-prodesign.net Fanel Florin Dediu

    So, where is that $alt variable? I recive next error:

    Notice: Undefined variable: alt

  • http://e-prodesign.net Fanel Florin Dediu

    I have 2 problems:

    1. $alt Undefined variable

    2. I can edit the table but i have no changes in mysql DB. Somenone comment that $field variable is the problem. Can you give us more details?

  • http://e-prodesign.net Fanel Florin Dediu

    What we need to do, for example, to change values on TAB key or a submit button? I’ve tried to change event to 9 but no change on database.

  • Rob

    After the table cell loses focus and the new value is populated, what would be the best way to change the font color of the new value so it stands out to the user?

  • Isuru Akalanka

    I had to add this to fix one issue with input box mouse click

    $(‘td.edit’).click(function () {
    if($(‘#editbox’).length>0) { return false }
    …….