jQuery And Ajax Tutorial - Dynamic Inline Form Editing

April 12, 2011 — This Jquery, Ajax tutorial will focus on creating a script using Jquery, Ajax and PHP that will dynamically update a MySQL database each time a user types in an input field. Included in the tutorial is a working example and script download.

Dynamic Input Fields with Jquery, ajax and php

In this tutorial we are going to show you how to use Jquery, Ajax and PHP to create a simple script that dynamically update a MySQL database each time a user types in an input field. By using this method the user isn't forced to refresh the page for simple changes while reducing the load on the server.

Click here to see a live example.

The default values in the form are loaded from an example database. You can update them, close the form, and re-open the page to see your values (You may see unfamiliar values if another user is also editing the database. Also, your browser, or the iframe used for this example, may cache values—if you think this may be happening, please refresh the page). To skip the walkthrough, just click the link below to download the example files:

Download:

ajax-update.zip

how does the script work?

There are two pages: ajax-form.php, and ajax-update.php. The first page contains a simple HTML form, a jQuery plugin to make it dynamic, and some PHP to get the current database table values. The second page contains only PHP, and it is the code that updates the database.

The HTML (and a little PHP)

Below is the PHP used to get the current values, and the HTML used to build the form.

HTML

The name attributes for the <input> elements correspond to the columns in your database. The hidden <input> element "#where" will tell ajax-update.php how to write the MySQL WHERE clause (the name attribute is the column to search for, the value is the row content to look for).

PHP

The second file, ajax-update.php simply connects to the database, and selects the current row from the database. It also places the row content into the value attributes of the <input> elements.

Each input field name is a database column name. The hidden #where input is the same.
  1. <?php // DATABASE: Connection variables $db_host = "localhost"; $db_name = "ex_database"; $db_username = "ex_user"; $db_password = "ex_pass"; // DATABASE: Try to connect if (!$db_connect = mysql_connect($db_host, $db_username, $db_password)) die('Unable to connect to MySQL.'); if (!$db_select = mysql_select_db($db_name, $db_connect)) die('Unable to select database'); // DATABASE: Get current row $result = mysql_query("SELECT * FROM user_table WHERE user_id=9"); $row = mysql_fetch_assoc($result); ?>
  2. <form id="ajax-form" class="autosubmit" method="POST" action="./ajax-update.php"> <fieldset> <legend>Update user information</legend> <label>Company:</label> <input name="user_company" value="<?php echo $row<'user_company'> ?>" /> <label>Name:</label> <input name="user_name" value="<?php echo $row<'user_name'> ?>" /> <label>E-mail:</label> <input name="user_email" value="<?php echo $row<'user_email'> ?>" /> <input id="where" type="hidden" name="user_id" value="<?php echo $row<'user_id'> ?>" /> </fieldset> </form>

Please input your own variables for accessing your database, or for larger projects, link to a separate file that accesses your database.

The jQuery Plugin

This next chunk of code is the jQuery plugin that runs the dynamic update call for each <input> field. It works as follows:

  1. Begin the jQuery plugin with standard syntax.
  2. Gather all the variables (the column names, form attributes, "where" details).
  3. When onBlur (when <input> loses focus), get the current <input> value.
  4. Then, send the variables to the same page from the form's action attribute.
  5. When the page call is successful, alert user if ajax-update.php produced output.
  6. Otherwise, notify user that the update was successful.

Notice: You will need the jQuery library for this to work. The example files download includes a link to the jQuery library hosted by Google.

All variables are from the HTML. Be sure to write a proper success function.
  1. (function($) { $.fn.autoSubmit = function(options) { return $.each(this, function() { // VARIABLES: Input-specific var input = $(this); var column = input.attr('name'); // VARIABLES: Form-specific var form = input.parents('form'); var method = form.attr('method'); var action = form.attr('action'); // VARIABLES: Where to update in database var where_val = form.find('#where').val(); var where_col = form.find('#where').attr('name'); // ONBLUR: Dynamic value send through Ajax input.bind('blur', function(event) { // Get latest value var value = input.val(); // AJAX: Send values $.ajax({ url: action, type: method, data: { val: value, col: column, w_col: where_col, w_val: where_val }, cache: false, timeout: 10000, success: function(data) { // Alert if update failed if (data) { alert(data); } // Load output into a P else { $('#notice').text('Updated'); $('#notice').fadeOut().fadeIn(); } } }); // Prevent normal submission of form return false; }) }); } })(jQuery);

To understand more about the .ajax() function, how it works, and simpler alternatives, please read our Ajax and jQuery Tutorial.

The jQuery Code Snippet

The jQuery necessary to initiate the autoSubmit() is very simple. When the document is ready, just call .autoSubmit() on every input field that should perform a dynamical update to the database.

This will tell each input element to automatically submit its value after data is entered.
  1. $(function(){ $('#ajax-form INPUT').autoSubmit(); });

The PHP

The second file, ajax-update.php performs only two functions:

  1. Connect to the MySQL database.
  2. Update the row in the table (if all variables were supplied).
Each input field name is a database column name. The hidden #where input is the same.
  1. // DATABASE: Connection variables $db_host = "localhost"; $db_name = "ex_database"; $db_username = "ex_user"; $db_password = "ex_pass"; // DATABASE: Try to connect if (!$db_connect = mysql_connect($db_host, $db_username, $db_password)) die('Unable to connect to MySQL.'); if (!$db_select = mysql_select_db($db_name, $db_connect)) die('Unable to select database'); // FORM: Variables were posted if (count($_POST)) { // Prepare form variables for database foreach($_POST as $column => $value) ${$column} = $value; // Perform MySQL UPDATE $result = mysql_query("UPDATE user_table SET ".$col."='".$val."' WHERE ".$w_col."='".$w_val."'") or die('Unable to update row.'); }

Please input your own variables for accessing your database, or for larger projects, link to a separate file that accesses your database.

before you Download the script

Notice

You will need the jQuery library for this to work. The example files download includes a link to the jQuery library hosted by Google.

Download:

ajax-update.zip

Add a Comment

Comments

Posted Subject Comment
Matthew
November 22, 2011
Running multiple instances ... I love this script! I'm in the middle of attempting to make it multiple instance friendly. Let me know if you have any tips for how to accomplish this since I'm a javascript newbie. :)
hahoxkyhx
November 16, 2011
HundfEfSkTmfkAxsa XEXNhD <a href="http://qjkxrytvnynb.com/">qjkxrytvnynb</a>, <url=http://lpaxxfwevznl.com/>lpaxxfwevznl</url>, <link=http://alruibwhjzsi.com/>alruibwhjzsi</link>, http://xdsvsxuzxdyi.com/
paul
November 10, 2011
ajax form working with text... Does anyone know how to get this working with textarea rather than input field
David
September 06, 2011
Ajax update Great script, thanks for that. Only problem I'm having is the solution for checkbox buttons, but it seems you're already working on that also, which I am really looking forward to.
Author
August 30, 2011
In reply to: Anusha
Submit button in the form
Needs different JS and PHP
This tutorial is meant to remove the necessity for the submit button. To have a form that works via ajax with a submit button, I'd recommend replacing the original Javascript with the following snippet of JS code:

/*
* onSubmit: Submit 'form_id_here' form with Ajax
*/
$('#form_id_here').live('submit',function() {
// Store values of the form
var form = $(this);
var values = form.serialize();
// Send via Ajax
$.ajax({
type: 'POST',
url: 'page_to_parse_form_then_output_html.php',
data: values,
success: function(data){
var div = form.parents('#form_container_div_id_here');
div.html(data);
}
});
return false;
});

This code will submit your form to a page you define, then use any html output from that page to replace the form. Your form must be inside a container tag element for this code to work as expected.

The .php page you build should simply parse the form as you would without Ajax. When the form parse is complete, you can simply have the page print an error or success message, or even show the form again with the new values.
Author
August 30, 2011
In reply to: Marcos
loop to update several rows
Change the WHERE clause
You can change the value for one column on all rows in two ways. The most secure way is to change the ajax-update.php file.

Where the SQL is:

"UPDATE user_table SET ".$col."='".$val."' WHERE ".$w_col."='".$w_val."'"

You could try:

"UPDATE user_table SET ".$col."='".$val."' WHERE ".$col." IS NOT NULL"

That should update COL with VAL for any cell that is not the NULL value. If your database table contains NULL values that you'd like changed, you could instead try:

"UPDATE user_table SET ".$col."='".$val."' WHERE a_column_whose_value_is_never_empty=''"

This would update COL with VAL for any cell in the WHERE clause column that is not empty (i.e. equal to '').
Using either of these methods, would allow you to remove the:

<input id="where" type="hidden" name="user_id" value="<?php echo $row<'user_id'> ?>" />

from ajax-form.php, because it will be useless.
Anusha
August 22, 2011
Submit button in the form How can I write the event if there is a submit button in the form and I don't need the blur event for each and every input.
Marcos
August 21, 2011
loop to update several rows What if i select all rows from mysql and then change value (and tab out) in one column for all rows. In this case this script doesn't work. I think mysql query in ajax-update.php must be updated to loop over the rows - and also jquery-ajax script? this i'm not certain. thanks for any info.
Author
July 11, 2011
In reply to: Matt Lindahl
Different Input Types
Not available yet
Yes this script only works for standard inputs. We are in the process of creating an advanced tutorial that describes the methods for dynamic input of select menus and radio / checkbox input
Matt Lindahl
July 10, 2011
Different Input Types I've gotten this script working with the normal Input type, but I can't seem to get it working with Checkbox or Select (radio button) types. Would there be anything else that would have to be done, or am I just plain doing something wrong?

Thanks for the help!
Author
May 29, 2011
In reply to: Live
Hi, it seems updating but no changes in DB.
Try some Ajax error checking
First Check: Add an "alert(somevariablename);" to the javascript just before "// ONBLUR" to make sure jQuery is getting the variable values.

Second Check: If jQuery is getting the variable values, you can have the PHP page output variable values also. Ajax will alert any output from the PHP page.
Live
May 28, 2011
Hi, it seems updating but n... Hi, first of all, this is a fantastic script!

I'm so lucky I found your work. Thanks, I really appreciate the hard work. :)

However, I was able to put things together, and retrieve values from the database.

But when I change something, for example, "customer_name", it says, Updated on the bottom-left.

But when I look at the phpmyadmin database, there are no changes.

What could be wrong?

12