Using an Ajax powered web form to update a database

As part of the preparation for my upcoming wedding to a wonderful lady, I’m in charge of building our wedding website, which we’ve decided will handle all of our RSVPs. I’m still in the process of building the RSVP form for that site, maybe I’ll do a full post about it and all its features once it’s done. But this process did give me an idea to build a more universal and flexible web form, which could easily be plugged into any LAMP environment and used to push form responses to a database using Ajax.

[Skip all this and go straight to the code on GitHub]

My goal here was to build a very minimal HTML form that would be easy to change, add to, style, and insert into any website or CMS system, and then build the supporting JavaScript and PHP to add all the interactive functionality of the form. I ended up with a highly flexible solution with the following features:

  • Dynamically supports the addition of new <input> and <select> tags to the form. Just add the new items to the HTML and the JavaScript and PHP will take care of adding them to the database.
  • Integrated reCAPTCHA, easy to enable/disable
  • Basic pre-submit validation available by wrapping fields in a “required” class
  • Dynamically creates a table in the database if it doesn’t already exist
  • Dynamically creates a column in the table for each field (based on the name attribute) if one does not already exist

Setting it up

If you want to use this form on your site, you’ll need to make a few minor changes. First, in form.php, you’ll need to change the following lines that appear at the top of the file:

I think this is pretty self explanitory. If you don’t know your server name, it’s probably “localhost.” Make sure you have a database set up, and a user account that can access it, and place that data in the corresponding spot. Then choose any name for your table, or use the name of an existing table.

For reCAPTCHA

If you want to use reCAPTCHA validation, you’ll have to sign up for a reCAPTCHA API key, and use the private key you’re provided to fill out the next line in the code:

And you’ll also need to change 3 lines near the top of /js/form.js:

Set DBForm.recaptcha = true;, insert your reCAPTCHA public key where shown, and edit the reCAPTCHA theme if you so choose.

Once that’s done, you’re ready to go. Presumably you’ll want to edit the HTML so the form contains the fields you want, but that’s all you have to do. No more updates to the JavaScript or PHP are required Just upload the files to your server, and start using the form. Note that if you upload the files into different directories, you’ll have to update some file references in the HTML and JavaScript.

The HTML

For the base HTML, I built the form as a table, with each table row containing one form field. However, since the JavaScript that interprets the form is flexible and based on tag names and class names, this style of setup is not required. The only real requirements, if you want to avoid editing the JavaScript at all, are that you keep all of your form fields inside a container with an id attribute set to “dbform-fields” and that you give each field a unique name attribute (except for named radio groups, which should share a name as normal).

This setup also includes some basic client-side validation, which can be easily built upon without touching the JavaScript. By putting a field and its label inside a container that has a class of “required” when the validation runs, that field will be included (I recommend creating a separate “required” container for each required field, as opposed to putting multiple required fields in one container). If an item fails validation, two things will happen:

  • Any element with a class “formLabel” that is inside a “required” container will have its text color set to “red”
  • The container with an id “validationMessage” will be displayed

The other thing to keep in mind is that whatever element has an id of “dbform-submit” will trigger the form submission when clicked. By default, this is the “Submit” button, but it could be changed to anything.

For reCAPTCHA

If you’re using reCAPTCHA, you’ll need to adhere to a couple more rules. Most importantly, you need to have an empty container in the HTML with an id of “dbform-recaptcha” for the reCAPTCHA form to be inserted into. If a submission fails because the reCAPTCHA was incorrect, a new reCAPTCHA will be generated on the page, and the container with id “dbform-recaptchavalidation” will be displayed.

The JavaScript

Most of the magic in this project takes place in the /js/form.js file. The first thing I did in there was create an object called “DBForm” at the global scope so all subsequent functions and variables could be children of that object instead of adding to much to the global level. I then created and new child object called “FormFields” that will eventually store all the data from a submitted form.

Once the DOM is loaded, all we need to do is attach the submit functionality to the right button. In this case I used the jQuery on() handler, in case anyone wanted to get tricky with loading content and the submit element would be unavailable at $(document).ready(). We also instantiate the reCAPTCHA form, if it is being used.

The DBForm.StartSubmission() function is pretty simple, it just calls other functions:

The DBForm.GetSubmittedFields() is the most complicated piece of this package, it bundles the values for every field on the form into the DBForm.FormFields object. The first thing it does is singles out “radio” type inputs, as they behave significantly differently than all other field types. I didn’t want to create an entry for each radio button, I wanted to create an entry for each named radio group and have it populated with the value of the selected radio item.

I did this by looping through all <input> tags with a type of “radio” and then excluding all the unselected ones. For each selected radio button, I created a new item with that radio button’s name and value attributes. I then added all of these items to the DBForm.FormFields object.

Notice that I’m defining a “type” here. That type will be used if the PHP needs to build a column in the database to correspond to this field. It will be set as the MySQL data type.

With radio buttons taken care of, the rest of the inputs are much easier. I still divided up checkboxes and textareas from the rest because I wanted to give them different value types.

And together those sections make up the DBForm.GetSubmittedFields() function, and populate the DBForm.FormFields object with the necessary data from every form field.

The next function called by DBForm.StartSubmission() is the client side validation function, DBForm.ValidateClient(). It returns true if all required fields have a value, otherwise it returns false and performs the validation actions, which consist of changing some colors and displays some validation text. It also clears out the DBForm.FormFields object so it can be re-populated when the form is submitted again.

If the form doesn’t pass validation, then the user is prompted to fill in the required fields and can submit the form again. If it does pass, then we move on into the DBForm.SubmitForm() function, which sends the form data to the server in the form of an Ajax POST request.

The JSON2 library is used to convert the DBForm.FormFields object to a JSON string that can be passed to the server as part of the POST data. Additional data is sent if the form is using reCAPTCHA. If it is, and the reCAPTCHA response is incorrect, the reCAPTCHA validation function DBForm.RecaptchaFail() is called. Otherwise we move onto the success callback.

Note that if the HTML file you’re running this from isn’t in the same directory as form.php, you’ll need to edit the “url” parameter of the above function.

At this point, I haven’t created a success callback function that does more than alert “Success!” I’m not sure if it’s worth developing one, as I’m sure most implementations of this will vary on how they handle it. I thought about redirecting to a success page, but if you’re doing that then you might as well forgo the Ajax and just build a straight PHP form with a post action.

The DBForm.RecaptchaFail() just shows the reCAPTCHA validation message and reloads a new reCAPTCHA so the form can be submitted again. Like the client validation function, it clears out the DBForm.FormFields object.

The PHP

The PHP writing to the database is surprisingly short, considering how dynamic the possibilities are with this form. With a simple foreach loop, everything sorts itself out.

The first thing that needs to be sorted out is whether or not the form was submitted with reCAPTCHA. All we have to do for this is check for the reCAPTCHA related POST variables. If reCAPTCHA is in use, we include the required library and send off a validation request. If it passes (or if reCAPTCHA is not in use) we continue on to update the database, otherwise we exit and pass a failure message back to the browser that it can use to start DBForm.RecaptchaFail().

The InsertRowFromForm function does a lot of work with very few lines of text. The essential goals of the function are to:

  • Create the appropriate table in the database if it does not already exist
    • Create a column in that table for a datetime stamp
  • Iterate through each field included in the POST data
    • If the table does not contain a column for the field, create one using the field’s “type” definition as the data type
  • Create a new row in the database, using the values from each field in the POST to populate its corresponding column.

The first step is accomplished quickly by making a MySQL CREATE TABLE statement and adding the IF NOT EXISTS parameter.

Now that we can be sure that the required table exists, we can start putting together our list of column names and values to insert. We first create a datetime stamp to use for the “Date” column that was created with the table, and use it to begin our collections of names and values.

And then we take advantage of the awesome portability of JSON and decode the “fields” data from the POST into a PHP object.

With all the form fields set up as an object, we can iterate over each property and add them one by one to our list of columns. First we attempt to add a new column for each item, using a try { ... } catch { ... } setup so that the function will continue on even if the column already exists and causes an error to be thrown. Once we’re sure the column is there, we add the column name and value to our growing strings.

Now it’s just a matter of removing the last trailing comma from our prepared strings of column names and values and inserting them into a SQL statement to add a new row to the table. Once that’s accomplished, we can close the connection to the database, and end the function.

And at that, it’s done! 119 lines of JavaScript, 55 lines of PHP, and we have a fully functioning, highly configurable, Ajax powered database web form.

Tagged with: , , ,
Posted in GitHub, HTML & CSS, JavaScript, MySQL, PHP, Server Side Code

Leave a Reply

Your email address will not be published. Required fields are marked *

*