Testing Times - MySQL

Once the form is submitted, the script "apply_rslt.php" takes over. The function of this script is to verify the data entered into the form, by ensuring that all required fields are present and in the correct format, and enter this data into the database.

<?
// apply_rslt.php - insert form data
// includes
// error checks
// open connection to database
$connection = mysql_connect($hostname, $user, $pass) or die ("Unable to
connect!");
// get job details
// use a join to get data from different tables
$query = "SELECT designation, jcode, department from listing, department
WHERE jcode = '$jcode' AND department.id = listing.fk_department";
$result = mysql_db_query($database, $query, $connection) or die ("Error in
query: $query. " . mysql_error());
// obtain data from resultset
list($designation, $jcode, $department) = mysql_fetch_row($result);
mysql_free_result($result);
// snip
?>

As always, there are the obligatory error checks to ensure that the job code (passed as a hidden value from the form) is valid.

Next, an array is created to hold error messages, and the various required text fields are validated. If errors are found, the error messages are added to the array for later display.

<?
// snip
// set up error list array
$errorList = array();
$count = 0;
// validate text input fields
if (empty($fname)) { $errorList[$count] = "Invalid entry: First name";
$count++; }
if (empty($lname)) { $errorList[$count] = "Invalid entry: Last name";
$count++; }
// snip
if (empty($email) || isEmailInvalid($email)) { $errorList[$count] =
"Invalid entry: Email address"; $count++; }
// snip
?>

The empty() function is used to test whether or not a variable contains a value, while the is_numeric() and is_string() functions are used to test whether a value is a number or a string. As you can imagine, these built-in functions come in very handy when testing for valid data in a form.

The isEmailInvalid() function is a custom function, written to test whether the email address matches a standard pattern.

<?
// check if email address is valid
function isEmailInvalid($val)
{
// regex for email validation
$pattern =
"/^([a-zA-Z0-9])+([\.a-zA-Z0-9_-])*@([a-zA-Z0-9_-])+(\.[a-zA-Z0-9_-]+)+/";
// match?
if(preg_match($pattern, $val))
{
return 0;
}
else
{
return 1;
}
}
?>

I also need a check to ensure that the user has not already applied for this job (this is a very primitive check, performed on the basis of the user's email address.)

<?
// snip
// check to ensure that user has not already applied for same job
if (!empty($email))
{
$query = "SELECT email from r_user WHERE email = '$email' AND jcode =
'$jcode'";
$result = mysql_db_query($database, $query, $connection) or die ("Error in
query: $query. " . mysql_error());
if (mysql_num_rows($result) > 0)
{
$errorList[$count] = "Duplicate entry: An application for this job
already exists with the same email address";
$count++;
}
}
// snip
?>

Next, the various multiple-entry fields - education, skills, references - are evaluated.

<?
// snip
// validate multiple-record items
/*
1. get number of entries possible (rows)
2. check to see if any text field in that row is filled up
3. if yes, ensure that all other fields in that row are also filled
4. if no, go to next row and repeat
*/
// check education listings
for ($x=0; $x<sizeof($institute); $x++)
{
if(!empty($institute[$x]) || !empty($degree_year[$x]))
{
if(empty($degree[$x]) || empty($degree_year[$x]) ||
!is_numeric($degree_year[$x]))
{
$errorList[$count] = "Invalid entry: Educational 
qualifications, item " . ($x+1);
$count++;
}
}
}
// similar checks for employment, skills and references
// snip
?>

During the development exercise, the various error checks may appear tiresome; however, they are, by far, the most crucial part of this script. If the data entered into the form is not validated properly, you will begin seeing invalid or incomplete data in your database; this affects the integrity of your data structures, and the efficiency of your search queries.

Make your validation routines as stringent as possible, and try to cover all your bases. Paying insufficient attention to this can lead to sleepless nights and splitting headaches. And after you're done writing them, give your friendly neighborhood hacker a few bucks and see if he can get past them.

This article copyright Melonfire 2001. All rights reserved.

>>> More MySQL Articles          >>> More By icarus, (c) Melonfire

blog comments powered by Disqus