Home arrow MySQL arrow Page 10 - The Perfect Job (part 1)

Testing Times - MySQL

Recruitment - the art of matching qualified applications to openpositions within an organization - is one of the most challenging tasks forany Human Resources department. However, powerful open-source tools likePHP and mySQL have made the process simpler, more efficient and moreeconomical than at any time in the past. This case study demonstrates how,by building a complete job listing and resume management system fromscratch.

  1. The Perfect Job (part 1)
  2. An Ideal World
  3. Entry Point
  4. Going To The Database
  5. The Five Rs
  6. Lucky Thirteen
  7. Building The Foundation
  8. The Devil Is In The Details
  9. Applying Yourself
  10. Testing Times
  11. Filing It All Away
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 4
June 28, 2001

print this article


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
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: