JavaScript: Making sure form values are unique using Ajax
When creating a database it's often necessary to set certain fields to accept only UNIQUE values. Typical examples would be: the name of a category; a username; or the SKU or ISBN of a product.
Trying to insert duplicate values into a UNIQUE field will generate an error, so it should really be checked every time before data is inserted. In addition to doing this on the server side - after a form has been submitted - it's now also possible to check for uniqueness while the user is filling out the form.
The checkUniq() JavaScript function
First we include our AjaxRequest class and then set up a JavaScript function to pass values to the server side script that will check with the database whether the value being entered already exists:
<script src="ajaxrequest.js"></script>
<script>
var checkUniq = function(field, value) {
var req = new AjaxRequest();
req.setMethod("POST");
var params = "table=table&field=" + encodeURIComponent(field) + "&value=" + encodeURIComponent(value);
req.loadXMLDoc("checkuniq.php", params);
};
</script>
The parameters being transmitted are:
- table: the name of the database table;
- field: the name of the field in table that needs to be UNIQUE; and
- value: the value about to be inserted or updated.
We are assuming for now that the name of the table can be hard-coded, but it could just as easily be a variable passed to the script. Normally a given page/form will only be updating a single table, but if you wanted to use the same JavaScript for a number of pages/tables then it would need to be a parameter.
This function can now be called from any text input field using the onchange event handler. Entering a new value passes the name of the input box, which we're assuming matches the database field name, and it's value:
<p>Username: <input type="text" name="username" id="field_username" onchange="
checkUniq(this.name, this.value);
"></p>
The onchange event handler in this case will pass the name ('username') and value of the input box to the checkUniq function. The id assigned to the input box ('field_username') is our handle for referring to it later using Ajax.
The checkuniq.php PHP script
All we need now is a server side script checkuniq.php that accepts table, field and value as $_POST parameters and returns the appropriate XML-encoded instructions.
Here is an example of how such a script might look:
<?PHP
include "class.xmlresponse.php";
// white lists for table and field input values
$allowed_tables = array('table1', 'table2');
$allowed_fields = array('username', 'email');
// validate script inputs
if(!isset($_POST['table']) || !($table = $_POST['table']) || !in_array($table, $allowed_tables)) die();
if(!isset($_POST['field']) || !($field = $_POST['field']) || !in_array($field, $allowed_fields)) die();
if(!isset($_POST['value'])) die();
$value = $_POST['value'];
function isDuplicate($table, $field, $value)
{
if(!$value) return false;
//
// insert your SQL query and PHP code to check for duplicate values
//
// if a matching $value for $field exists in $table, return true;
// otherwise, return false;
//
}
$xml = new \Chirp\xmlResponse();
$xml->start();
// generate commands in XML format
if(isDuplicate($table, $field, $value)) {
$xml->command('alert', array('message' => "Sorry, the $field '" . stripslashes($value) . "' is already in use!"));
$xml->command('setdefault', array('target' => "field_{$field}"));
$xml->command('focus', array('target' => "field_{$field}"));
}
$xml->end();
?>
First we have some important code for validating the input. You don't want people to be able to query your whole database so what we've done here is limit the input values for table and field to pre-set lists. We're requiring that all three parameters are set and that the first two have only allowed values.
After that it's simply a question of querying the database to determine whether the value to be inserted is already present. The simplest solution to this is to run an SQL query as follows:
SELECT COUNT(*) AS count FROM $table WHERE $field='$value';
Any value other than zero would indicate that the value already exists.
You can see now why it's so important that we limit the inputs for $table and $field to avoid SQL injection vulnerabilities. You don't want just anything to be able to be inserted there. The $value parameter should also be escaped using the relevant function.
PostgreSQL:
SELECT COUNT(*) AS count FROM $table WHERE $field='" . pg_escape_string($conn, $value) . "';
MySQL:
SELECT COUNT(*) AS count FROM $table WHERE $field='" . mysql_real_escape_string($value) . "';
XML commands returned by checkuniq.php
If there is no problem with duplicate values then an empty XML file is returned. If there is a duplicate, however, the script returns a series of commands as follows:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<response>
<command method="alert">
<message>Sorry, the username 'username' is already in use!</message>
</command>
<command method="setdefault">
<target>field_username</target>
</command>
<command method="focus">
<target>field_username</target>
</command>
</response>
These commands are then executed by our AjaxRequest JavaScript class as follows:
- Display an alert message: "Sorry, the username 'username' is already in use!"
- Set the contents of the input field back to it's default value; and
- Set the focus back to the input field.
Links to information on these and other available commands in the AjaxRequest class can be found under Related Articles below.
Related Articles - Ajax
- JavaScript Form Validation using Ajax
- JavaScript Making an asynchronous request before following an href
- JavaScript Using a Promise to make sequential Ajax requests
- JavaScript Making sure form values are unique using Ajax
- JavaScript Avoiding the Race Condition with Ajax
- JavaScript Using XMLHttpRequest to log JavaScript errors
- JavaScript Recording Outbound Links using Ajax
- PHP Generating an XML Response for Ajax Applications
- JavaScript Web Services using XMLHttpRequest (Ajax)
SuperDogStar 5 February, 2016
I know this post is old, but is there any chance you could post an example of what the isDuplicate function would look like? I've tried a few different things, but can never get it to return true to the if(isDuplicate... statement. Thanks!
Maybe if you post what you have. It shouldn't be difficult.
Human 5 April, 2012
Great site and very complete explanation on how to use it. Took me a few minutes to figure out that I needed to remove the field_ in front of the "$xml->command"'s target to get it to work with my form but with the help of Firebug it now works great. Sites like yours are invaluable to folks learning on the fly. Thank you.
Himanshu 2 December, 2010
include "class.xmlresponse.php"
please mention the definition of this class as well as without this the tutorial is incomplete
You can find the code for class.xmlresponse.php presented here
Don Keruskie 10 April, 2010
I was hoping that you might be able to tell me where, in step 2 of this tutorial, I put the SQL query. Does it go in checkuniq.php, or the php page with my form?
It goes in "checkuniq.php". This is the server-side script called by the onchange event using Ajax. You can't run PHP in an already-loaded page.