Save to database via Ajax using jQuery, php, mysql

Thursday, 21 January 2010


When you start building your very first php + mysql applications you will likely have a loop that looks something like this:
* Display a form
* Submit a form to php page
* Update database
* Return user to form, possibly with success message

This is a good basic loop and works perfectly fine in very small scale sites or for something like a user sign-up form to a newsletter etc. But as web applications have moved forward and there is a greater focus on more fluid and dynamic applications form processing has also progressed and it is now possible to submit a form without reloading your page.


Think of the situation where someone has to use your web application to do a large amount of data entry or providing inline editing of a table. You wouldn't want your valuable users to be waiting while the page loads each time.

This tutorial will show you how you can process your form using Ajax.

A working version of this functionality can be viewed here.
You can download the code for this tutorial here

Our new loop will look like this:
* Display a form to the user
* Validation is performed on the form before submit, displaying an alert dialog if validation fails
* Form details are then sent to a php script via AJAX
* php script attempts to save to database and reports if there were any errors
* page is updated (form reset and success message) if successful or display error if save failed

To begin with we have a relatively plain HTML form:
<form id="personal_details" action="process.php" method="post">
 <fieldset>
  <legend>Personal Details</legend>
  <label for="name">Name</label>
  <input type="text" name="name"/>
  <label for="age_range">Age</label>
  <select name="age_range">
   <option value="">Please select an age range</option>
   <option value="1">0-12</option>
   <option value="2">12-16</option>
   <option value="3">16-21</option>
   <option value="4">21-30</option>
   <option value="5">30-50</option>
   <option value="6">50+</option>
  </select>
  <label for="sports">Please select your favourite sports</label>
  <input type="checkbox" name="sports[]" value="soccer" />&nbsp;Soccer<br />
  <input type="checkbox" name="sports[]" value="swimming" />&nbsp;Swimming<br />
  <input type="checkbox" name="sports[]" value="cycling" />&nbsp;Cycling<br />
  <input type="checkbox" name="sports[]" value="kayaking" />&nbsp;Kayaking<br />
  <input type="checkbox" name="sports[]" value="volleyball" />&nbsp;Volleyball<br />
  <input type="checkbox" name="sports[]" value="basketball" />&nbsp;Basketball<br />
  <button type="button" onclick="processDetails();">Submit</button>
  <div style="clear:both;"></div>
 </fieldset>
</form>



You can see we have a Submit button that calls the processDetails function. We also set processDetails as the submit function in the jQuery document ready function as such:
$("document").ready(function() {
 $("#personal_details").submit(function() {
  processDetails();
  return false;
 });
});

Now lets have a look at our processDetails function;
function processDetails() {
 var errors = '';
 
 // Validate name
 var name = $("#personal_details [name='name']").val();
 if (!name) {
  errors += ' - Please enter a namen';
 }
 // Validate age range
 var age_range = $("#personal_details [name='age_range']").val();
 if (!age_range) {
  errors += ' - Please select and age rangen';
 }
 // Validate sports selection
 var sports = $("#personal_details [name='sports[]']:checked").length;
 if (!sports) {
  errors += ' - Please select your favourite sportsn';
 }
 
 if (errors) {
  errors = 'The following errors occurred:n' + errors;
  alert(errors);
  return false;
 } else {
  // Submit our form via Ajax and then reset the form
  $("#personal_details").ajaxSubmit({success:showResult});
  return false;
 }
 
}

processDetails begins with our validating our three form inputs. We basically just check to see if they are set and if they have not been set we display an error message. If no errors are called we submit the form to Ajax by calling the ajaxSubmit function. ajaxSubmit is part of the jQuery forms plugin. This plugin has many great functions that we can use for processing forms. The ajaxSubmit function looks at the form elements action attribute and uses that to submit your form via Ajax so we don't need to worry about serializing and submitting the form manually, it all gets taken care of. I also pass in a success function showResult which will be called on Ajax submit success.

function showResult(data) {
 if (data == 'save_failed') {
  alert('Form save failed, please contact your administrator');
  return false;
 } else {
  $("#personal_details").clearForm().clearFields().resetForm();
  alert('Form save success');
  return false;
 }
}

On success we either display a success or failure alert message. If successful we also clear the form using the form plugins clearForm, clearFields and resetForm functions.

So now anything that happens in our php script is going to happen in the background after it has been called via Ajax.

In our php file we are going to do the following:
* Retrieve our data from the form post ($_POST)
* Check that we have been provided the amount of data we expected
* Try connecting to the database
* Clean the data from the post to ensure no attacks (please not in this example the cleaning is not extensive, you will need to learn about cleaning variables for database inserts elsewhere)
* Attempt to insert the data into the database

You will see that when there is an issue anywhere in the script we echo out 'save_failed' so that the javascript function showResult will know that there has been an issue.

Furthermore with any of the myqsl functions I have appended an @ to the front of the function call, this will supress any error messages given if accessing the database fails.

Let's have a look at the code:
// Retrieve form data
$name = $_POST['name'];
$age_range = $_POST['age_range'];
$sports = $_POST['sports'];
if (!$name || !$age_range || !$sports) {
 echo "save_failed";
 return;
}

// Convert sports array to a serialized string
$sports_list = serialize($sports);

We begin by retrieving the variables from the post and checking that they have been set.

Next we attempt to connect to the database, the details of your database will be provided by your host or depending how you have set them on your own personal server:
$db = array(
 'host' => 'host here',
 'login' => 'username here',
 'password' => 'password here',
 'database' => 'database here',
);
$link = @mysql_connect($db['host'], $db['login'], $db['password']);
if (!$link) {
 echo "save_failed";
 return; 
}
mysql_select_db($db['database']);

As mentioned, you will need to clean your variables to avoid database attacks. Begin by reading this to get a good idea of what's required. CakePHP has a very extensive cleaning function built in and is worth looking at if you want to do some further research.
// Clean variables before performing insert
$clean_name = mysql_real_escape_string($name);
$clean_age_range = mysql_real_escape_string($age_range);
$clean_sports_list = mysql_real_escape_string($sports_list);

Our last step is doing the actual insert into the database:
// Perform insert
$sql = "INSERT INTO details (name, age_range_ID, sports) VALUES ('{$clean_name}', {$clean_age_range}, '{$clean_sports_list}')";
if (@mysql_query($sql, $link)) {
 echo "success";
 @mysql_close($link);
 return;
} else {
 echo "save_failed";
 @mysql_close($link);
 return;
}

This is fairly straight forward; I try and run the query and then return the appropriate message and lastly close the database connection.

Hopefully this tutorial gives you some good insight into getting started on adding Ajax database updates to your web applications.


blog comments powered by Disqus