PHP & jQuery: Adding Inline Edit To AJAX Add & Remove MySQL Table Entries

Posted 3 months, 1 week ago at 6:46 pm. 22 comments

Update: Version 2.1 of this script is now available. I highly recommend that you download the new version. You can get it here.

Ok. So I am going to show you quickly how to add inline editing to my previous tutorial on using jQuery to remove & add entries to a MySQL Table with AJAX. Inline editing is quite simply being able to edit the information on-the-fly. While this sounds easy, unlike the adding & removing it is actually quite hard and requires a rewrite of some of the code already written in the last tutorial. So anyway onwards…

Step One: Adding the PHP Code

First we do the easiest bit, adding the PHP code that will alter the database entry. To make the code easier to understand I am going to use smaller code boxes and explain little bits at a time. You need to add all of this code to the end of ajax.php:

1
2
3
4

if($_POST['action'] == "edit") :
	$content = $_POST['content'];
	$content = preg_replace("/[^a-z0-9 ']/is", "", $content);
	$content = addslashes($content);

Ok. So we check that we need to edit by checking the value of the ‘POST’ arrays ‘action’ entry. Next we assign the content to a variable called content from the ‘POST’ array. Then we do a ‘preg_replace’ to remove any malicious characters and then we add backslashes to any apostrophes as we have allowed them. You can add the apostrophes to the code in my previous tutorials if you like or you can download the new code at the bottom of this tutorial.

5
6
7
8
9
10
11

$db->query("UPDATE info SET name='".$content."' WHERE id = '".$id."'") or $failed = true;
	if($failed == true) :
		echo "<span class=\"failed\">Sorry, the update of that row failed. Please try again later.</span>";
	else :
		echo "<span class=\"success\">Update successful.</span>";
	endif;
endif;

This final bit is really simple and is nearly the same as the others. All we do is update the row in the database using the query written here. Change the table name if you need to. If it fails we set our error message which is written with Javascript a little later.

You will need to add this little bit of code to the top of ajax.php if it isn’t already written there:

1
2

$failed = false;
$id = $_POST['id'];

That is it for ajax.php.

Step Two: Adding The Extra JS Code

This is where the challenge comes in. Although I have called it adding the extra JS code it actually involves moving a little bit of the PHP code and adding a line or two. So first I need you to find this bit of code in list.php:

1
2
3
4
5

require_once("mysql.class.php");
 
$db =& new db_mysql("PASS", "USER", "PASS", "DB");
 
$r = $db->query("SELECT * FROM info ORDER BY id") or $db->raise_error();

Move it to the very top of the page. Yes, even above the doctype. Then copy the last line of that code and paste it above this line of code:

while($row = $db->fetch_array($r)) :

Next we need to add the extra JS code. Since we need to make it so that each table rows content can be edited we will need to give Javascript some way of recognising them. This is where jQuery’s class selection comes in handy. Find this bit of code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

$(document).ready(function(){
	$('input#entry').val('');
	$('input#entry').onkeydown = function(e){ 	
				if (e == null) { // ie
					keycode = event.keyCode;
				} else { // mozilla
					keycode = e.which;
				}
				if(keycode == 13){ // close
					ajaxAdd();
					return false;
				} 
			};
});

Replace it with the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

$(document).ready(function(){
	$('input#entry').val('');
	$('form').submit( function() {
		return false;
	});
	$('input#entry').keypress = function(e){ 	
				if (e == null) { // ie
					keycode = event.keyCode;
				} else { // mozilla
					keycode = e.which;
				}
				if(keycode == 13){ // close
					ajaxAdd();
					return false;
				} 
			};
 
	<?php while($row = $db->fetch_array($r)) : ?>
		ajaxEdit('edit<?php echo $row['id']; ?>', <?php echo $row['id']; ?>);
	<?php endwhile; ?>
});

The first bit of code is a better way of stopping the enter key from refresh submitting the form. The rest is actually a little bit of PHP mixed with Javascript and is the main reason for that little move around we had. All we are doing is calling the ‘ajaxEdit’ function for every time we have an row to edit. We hand the class of the table row and it’s row ‘ID’ I will explain more about that later when we actually make the function.

Ok. I will do this in bits again as it gets quite complicated. Here is the first bit:

1
2
3
4
5

function ajaxEdit(class, ids) {
	$('.'+class).click(function() {
		var textarea = '<div><input type="text" name="edit" value="'+$(this).html()+'" />';
		var button	 = '<div><input type="button" value="Save" class="saveButton" /> OR <input type="button" value="Cancel" class="cancelButton" /></div></div>';
		var revert = $(this).html();

First we set out function ‘ajaxEdit()’ we also hand across the class & id I was talking about before. Using jQuery’s class selector & the class sent across from before we select the current table row and assign an onclick function to it. Inside this function we set the textarea, which in my case is a textfield, HTML code & the button code for the save & cancel buttons. We also make revert equal the current td’s value so we can revert back if the user presses cancel.

6
7
8
9

                $(this).after(textarea+button).remove();
		$('.saveButton').click(function(){saveChanges(this, false, class, ids);});
		$('.cancelButton').click(function(){saveChanges(this, revert, class, ids);});
	})

We place the textarea and button after the td and then remove the td. Then we add the onclick actions to the save and cancel buttons. They both call the same function, which will be defined shortly, but with different options. The save button passes the object, sets cancel to false, passes the class & finally the id.

11
12
13
14
15
16
17
18

        .mouseover(function() {
		$(this).addClass("editable");
	})
 
	.mouseout(function() {
		$(this).removeClass("editable");
	});
}

Finally for this function we add a mouseover & mouseout function to change the class. This is entirely optional and could even be changed to something else.

20
21
22

function saveChanges(obj, cancel, class, ids) {
	if(!cancel) {
		var t = $(obj).parent().siblings(0).val();

First we make the new function ’saveChanges’. Then we check to see if cancel is set if it isn’t, we set ‘t’ to the parents, first siblings value, which would be the textbox. We use this method because it allows you to call all rows without being tied to a class name.

24
25
26
27
28
29
30
31
32
33

                $.post("ajax.php",{
		  action: 'edit',
		  id: ids,
		  content: t
		},function(txt){
			$('div#statusMsg').html(txt);
		});
	} else {
		var t = cancel;
	}

Next we make the ajax request but instead of using ‘$.ajax’ we use ‘$.post’. We do this because it can cope with the large amount of data better. We send the action, the id and the content. Then after the AJAX request finished we put the status message into the status div from last time, saying whether it failed or not. If cancel is set, we then set ‘t’ to equal to cancel which holds the revert info, revert is the original information held by the td.

35
36
37
38
39

        if(t=='') t='(click to add text)';
 
	$(obj).parent().parent().after('<td align="center" width="300px" class="'+class+'">'+t+'</td>').remove();
	ajaxEdit(class, ids);
}

Finally we check if ‘t’ is empty and add a little message if it is. We then target the tr which is the parents, parent of the div we are currently in and add the new td after it. Then we need to make this rows click function active again so we run the ‘ajaxEdit’ again for this row.

Right. I hope all that made sense. The code on the example has been updated and can be tried here. Also you can download the updated code just below:

Download: AJAX Edit, Add & Delete For MySQL Databases  AJAX Edit, Add & Delete For MySQL Databases (17.6 KB, 0 hits)

EDIT: Just realized that there is a problem with the code. Well not a problem but a bit I missed out. I forgot to make the edit refresh when you add a new item so until you refresh the page the new items are uneditable. I have now fixed this in the example & the download so here’s what to do to fix it. In ajax.php find this:

30
31
32
33
34

$html = '<tr class="'.$row['id'].'">
					<td width="20px">'.$row['id'].'</td>
					<td width="300px" align="center">'.$row['name'].'</td>
					<td><a width="40px" href="javascript: ajaxDelete('.$row['id'].');">Delete</a></td>
				</tr>|<span class="success">Excellent, your entry has been added successfuly.</span>';

Change it to this:

30
31
32
33
34

$html = '<tr class="'.$row['id'].'">
					<td width="20px">'.$row['id'].'</td>
					<td width="300px" class="edit'.$id.'" align="center">'.$row['name'].'</td>
					<td><a width="40px" href="javascript: ajaxDelete('.$row['id'].');">Delete</a></td>
				</tr>|<span class="success">Excellent, your entry has been added successfuly.</span>|'.$id;

Next in list.php find this:

104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122

function ajaxAdd() {
	toAdd = $('input#entry').val();
 
	$.ajax({
		type: "POST",
		url: "/wp-content/scripts/ajax.php",
		data: "action=add&amp;value=" + toAdd,
		success: function(msg) {
			if(msg.indexOf("|") != -1) {
				splitMsg = msg.split("|");	
				$('table#Stargate').append(splitMsg[0]);
				$('div#statusMsg').html(splitMsg[1]);
				$('input#entry').val('');
			} else {
				$('div#statusMsg').html(msg);
			}
		}
	});
}

Change it to this:

104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123

function ajaxAdd() {
	toAdd = $('input#entry').val();
 
	$.ajax({
		type: "POST",
		url: "/wp-content/scripts/ajax.php",
		data: "action=add&amp;value=" + toAdd,
		success: function(msg) {
			if(msg.indexOf("|") != -1) {
				splitMsg = msg.split("|");	
				$('table#Stargate').append(splitMsg[0]);
				$('div#statusMsg').html(splitMsg[1]);
				$('input#entry').val('');
				ajaxEdit('edit'+splitMsg[2], splitMsg[2]);
			} else {
				$('div#statusMsg').html(msg);
			}
		}
	});
}

Hopefully that should fix it. If you have any problems just leave a comment and I’ll get back to you ASAP.

Share and Enjoy: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • StumbleUpon
  • Google
  • Furl
  • del.icio.us
  • Facebook

22 Replies

  1. Mysql Download Gravatar Mar 29th 2008

    Hi Finally I found a site the has some good information on Mysql Download. I was searching around and found your post Remove MySQL Table Entries at Return True, thanks for the good info..I’ll be checkin back soon.

  2. Veneficus Unus Gravatar Apr 1st 2008

    No problem. Glad to see that someone is able to use the code I am writing. :D

  3. Mateus Gravatar Apr 9th 2008

    Excellent piece of work. Thanks a lot! :)

  4. Veneficus Unus Gravatar Apr 9th 2008

    @Mateus: No probs. I have actually improved the code a lot and am about to upload it in about 1 hours time so look out for that. ;)

  5. Scott Gravatar Apr 11th 2008

    Really nice work. Thanks. As a small note, you may want to change the js src in list.php to not include wordpress paths. Once I changed that it worked right off the bat.

    Thanks again.

  6. Veneficus Unus Gravatar Apr 11th 2008

    @Scott: Yeah sorry about that. I forgot to change it after I made the demo. I’ll update that now. Thanks for letting me know.

  7. wannabe Gravatar Apr 17th 2008

    hi.
    don’t really understand this stuff…

    where do we type where to store the data and stuff?? what rows and stuff.?

  8. Veneficus Unus Gravatar Apr 17th 2008

    Hi wannabe,

    You might want to try this code as it is a massive improvement in usability from this one.

    This version of the code only allows one table field due to a flaw in the coding that is why I am trying to get people to use my newer version at the link above.

    To put in the data you just have to change the table in the variable $html. Sorry, it wasn’t made to be very user friendly when I first made it. :(
    Thanks for your comment though and please give the newer one a try. ;)

  9. Kaye Billy Branks Gravatar Apr 18th 2008

    Hi ,
    This is some cool tutorialit has helped me to make my application more usable at First i Was using six pages to do editing ,deleting and addition u have saved my time.

    Big UP : JAH BLESS YA

  10. Veneficus Unus Gravatar Apr 18th 2008

    Thanks for your comment. That’s why I made it in hope someone else could get some use out of it as well. :)

  11. Joe Gravatar Apr 27th 2008

    Is the PHP written for PHP5 or is it compatible with PHP 4? I wrote and modified the original code a bit and got it work fine on my localhost which is running PHP 5, but when I moved it to my client’s server (which is running PHP4) it does not work.

  12. Leandro Gravatar May 10th 2008

    When I refresh listmulti.php I get “Click to edit” on each cells instead of the data. Any idea?

  13. Veneficus Unus Gravatar May 11th 2008

    @Leandro: If this is after you have tried to add something then the data may not be getting to your database & you could try checking your connection info.

    If it’s when you are trying to view already entered info then again it may be a connection problem, and although it sounds stupid, have you changed the field names in the table on the listmulti.php page?

    I hope that helps. If not anymore info you can give will help me a lot.

  14. julie Gravatar May 15th 2008

    hi this is really nice script i integrate with my code
    can you please tell me how can i run this in Internet Explorer this is working nicely in Mozile firefox
    not in IE please help me out

  15. Veneficus Unus Gravatar May 15th 2008

    Hi Julie,

    I can only test this code in IE 7 due to using Vista and it works great for me apart from a little bug when editing which I am currently busy trying to fix.

    Can you tell me what version of IE you are using and what problems you have?

  16. julie Gravatar May 16th 2008

    hi Veneficus Unus
    i also try this in IE7.i chage php file according to my database for edit on particular field and when i open my page in IE7 it is not allowing me to open even edit textbox. i m using this script only for edit not for add and delete.
    so plz try to help me

  17. Veneficus Unus Gravatar May 16th 2008

    Hi Julie,

    That’s a bit strange since it should work in all browsers as soon as you have entered your database information apart from the little bug I mentioned in IE7, but that won’t stop it from working…

    If you could give me a link to the page you are testing on I can help you a little more.

    Sorry I can’t help more at the moment. :(

  18. julie Gravatar May 16th 2008

    hi
    in IE7 edit functionality is not working at all
    when you open same page in MOzile its work grt

  19. julie Gravatar May 16th 2008

    hi
    http://mindplanner.nealinfotech.com/login.php
    this my login page for that site
    you put user id:julie_radadia@yahoo.co.in
    Password: julie

    then you click on action tab
    where you can see my data on TItle field i put edit functionality which is working grt in Mozile nt in IE&

    pplz help me out sn

  20. Veneficus Unus Gravatar May 16th 2008

    Hey Julie,

    I kind of understand what you are trying to do now. I eventually abandoned the idea of building my own inline edit and in the newer version of this code used a jQuery plugin called jEditable.

    You can either view how I used it here or you can view the original creators website here.

    I hope that helps a bit more.

  21. julie Gravatar May 17th 2008

    thanks veneficus unus
    that improve edit is working in both thanks a lot

  22. Veneficus Unus Gravatar May 17th 2008

    no problem Julie


Leave a Reply