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:
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.
Below is the PHP used to get the current values, and the HTML used to build the form.
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).
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.
- [?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);
?]
- [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.
This next chunk of code is the jQuery plugin that runs the dynamic update call for each [input] field. It works as follows:
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.
- (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 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.
- $(function(){
$('#ajax-form INPUT').autoSubmit();
});
The second file, ajax-update.php performs only two functions:
Each input field name is a database column name. The hidden #where input is the same.
- // 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.
You will need the jQuery library for this to work. The example files download includes a link to the jQuery library hosted by Google.
Let's get into contact so you learn more about us and we can create solutions for you!