mySQL Database Field Validation

I’ve broken this post down into a few main sections, so feel free to jump straight to the parts you are looking for. Section I is basic and will cover downloading and including the js scripts in your project; which most of you are likely familiar. Section II covers the basics of client side validation, starting with required fields, special fields like email, and some custom regex validation. Section III is the big one. I will show you how, and give examples of writing server side scripts to check against a database, and return to the form whether the entry is valid or not; all done with ajax so the user doesn’t need to submit the entire page unless it is ready to go.

Section I

I assume you already have a development environment setup for this project and won’t go over that as it isn’t part of the scope of this post. For example purposes all my javascript files are in the /js director relative to root and my coding and css is just in the project root.

If you don’t already have a copy of jquery you can swing by their website www.jquery.com to pick up their latest copy. The production version is what you are looking for right on their homepage. Put this file in your /js directory.

The real meat to this project comes from Jörn Zaefferer’s Validation plugin on his blog www.bassistance.de/jquery-plugins/jquery-plugin-validation/. Download a copy of it and also put it in /js with the jquery framework.

To include these files in your project simple put the following code into your header. I place these after my <title></title> and of course before </head>. Also make sure that you include the jquery file before the validation plugin otherwise validation will just error.

<script src="js/jquery-1.3.2.min.js" type="text/javascript"></script>
<script src="js/jquery.validate.pack.js" type="text/javascript"></script>

Section II

Now that our scripts are in place and loaded its time to show you how to use the validation plugin for basic client side validation as well as some custom regex powered validation.

To start add another <script> tag to the header of your project which we will layout our validation code.

$(document).ready(function() {
    // validate signup form on keyup and submit
    var validator = $("#reg_form").validate({
        rules: {
            reg_pcode: {
				required: true,
				pcode: true
			},
            reg_user: {
                required: true,
                minlength: 2,
                remote: {
				url: "db.php",
				type: "post",
				data: {
				type: "name",
				value: function() { return $("#reg_user").val(); }
				}
				}
            },
            reg_pass: {
                required: true,
                minlength: 5
            },
            reg_pass_firm: {
                required: true,
                equalTo: "#reg_pass"
            },
            reg_email: {
                required: true,
                email: true,
                remote: {
				url: "db.php",
				type: "post",
				data: {
				type: "email",
				value: function() { return $("#reg_email").val(); }
				}
				}
            },
            terms: "required"
        },
        messages: {
            reg_pcode: {
				required: "
You must supply a postal code.",
				pcode: "
You have not supplied a valid postal code."
			},
            reg_user: {
                required: "
You must supply a username.",
                minlength: jQuery.format("
Your username must be at least {0} characters long."),
                remote: jQuery.format("
Sorry but {0} is already in use. Please try another name.")
            },
            reg_pass: {
                required: "
Please specify a password.",
                minlength: jQuery.format("
Your password must be at least {0} characters long.")
            },
            reg_pass_firm: {
                required: "
Please repeat your password.",
                equalTo: "
our passwords do not match"
            },
            reg_email: {
                required: "
You must supply a valid email address",
                remote: jQuery.format("
{0} is already registered.")
            },
            terms: "
You must agree to the site terms"
        },
        // the errorPlacement has to take the table layout into account
        errorPlacement: function(error, element) {
            if ( element.is(":radio") )
                error.appendTo( element.parent().next().next() );
            else if ( element.is(":checkbox") )
                error.appendTo ( element.next() );
            else
                error.appendTo( element.parent() );
        },
        /* specifying a submitHandler prevents the default submit, good for the demo
        submitHandler: function() {
            alert("submitted!");
        },*/
        // set this class to error-labels to indicate valid fields
        success: function(label) {
            // set   as text for IE
            label.html(" ").addClass("checked");
        }
    }); 

});

The first line tells javascript to run the following function once the document is ready. We then create a new variable validator and give it the form element $(#’id’), running the validate function provided by the plugin validate().

The rules: {} option sets the requirements for each field by its id.

required: true    Tells the validator that the field is required (pretty obvious)

minlength: #    Tells the validator that there must be at least # characters

equalTo: “#id”    Tells the validator that this field must equal the other field with the supplied id

email: true    Tells the validator that this field must be a valid email address

remote: {}    Makes an ajax call and has a set of options all it’s own. This will be discussed in Section III

The messages: {} option tells the validator what reponses to give when the field doesn’t meet a rules: {} option. If you leave this blank it will supply a default response. You can use jQuery.format() with {#} to around your message to use field values in your response as per my examples with minlength: and remote:.

Now what if you want to use custom validation? Well we just need to use jQuery.validator.addMethod(name, method, message).

This first parameter name signifies the name of the rule that you will put into the rules: {} option in the previous code example.

The second parameter method is a method that supplies the validation and returns and true or false. It is easiest just to write the function in here unless you are already using it elsewhere. This functions first supplied parameter will be the current value of the field, the second will be the field element itself, and the third is the supplied parameter from the rules: {} option. For example ‘name’: ‘parameter’.

The final parameter message is the default message to supply if one isn’t set in the messages: {} option.

In my example I use regular expressions to validate a Canadian Postal Code using the following function.

function (value, element, param) {
   if (param) {
      return value.match(new RegExp("^[abceghjklmnprstvxyABCEGHJKLMNPRSTVXY][0-9][abceghjklmnprstvwxyzABCEGHJKLMNPRSTVWXYZ] {0,1}[0-9][abceghjklmnprstvwxyzABCEGHJKLMNPRSTVWXYZ][0-9]$"));
   }
}

This function call can apply to any regular expression match you want and for a great list of regular expressions just take a look at the Regular Expression Library over at www.regexlib.com

Section III

Finally to the good stuff. Probably the reason most of you have come to read this post. Database driven validation. Long ago are the times when web forms needed to be submitted before you got that dreaded response. Your username is already in use. That email has already been registered (common you know you’ve tried before). Well now with ajax these things are in the past. The jQuery validation plugin comes with ajax support via the previously mentioned remote: {} option. By default all you need according to their documentation is the following.

remote: {
   url: "serversidescript.php"
}

But I found this to be problematic for setup using a single server side script to make all database calls so I specified many more options for the benefit of my thought process. Here is what I used.

remote: {
   url: "db.php",
   type: "post",
   data: {
      type: "name",
      value: function { return $("#reg_user").val(); }
   }
}

The only requirement of the server side script is to output true or false which was pretty easy to accomplish with the following code.

query("SELECT * FROM users WHERE name='" . $_POST['value'] . "'")) {
		if ($mysqli->affected_rows > 0) {	// If rows affected then there was a match
			$valid = 'false';
		}
	}
} else if ($_POST['type'] == 'email') {		// Check against emails
	if ($result = $mysqli->query("SELECT * FROM users WHERE email='" . $_POST['value'] . "'")) {
		if ($mysqli->affected_rows > 0) {	// If rows affected then there was a match
			$valid = 'false';
		}
	}
}

$mysqli->close();
echo $valid;
?>

And that is all that is required. For non javascript guru’s this may look a little daunting but just give it a try and I think you’ll find it’s easier than you think.