Zen And The Art Of Recruitment
It might not seem all that hard, but manning an organization’s Human Resources division is actually a pretty difficult task. Sure, HR guys and gals don’t have to worry about memory leaks, garbage collection or mangled code; however, their problems are often just as compelling and challenging as those of any software programmer or interface designer.
One of the most challenging problems for any Human Resources department is recruitment, the art of matching potential employees with vacancies within the organization. The process is complex: place an advertisement, wait for resumes to roll in, and then begin the arduous task of sifting through them in search of potential recruits. This is usually followed by interviews and reference checks of potential candidates, and perhaps a job offer.
Now, back before the Web was a buzzword, the only way to let potential employees know about a job was via advertisements, either in a newspaper or journal, or on television. And once responses started rolling in, HR would have the unenviable task of wading through mounds of paper to find the perfect match for the open position. Along the way, you could almost always expect to misplace or destroy a few of those resumes, sustain multiple paper cuts, and say goodbye to any hope you might have had of a social life.
However, with everyone and their deaf grandma now connected to the Web, reaching out to potential candidates has become much easier. A company can now either advertise vacancies through online employment agencies, or place job listings on their own Web site. Since online employment agencies aren’t in the biz out of the goodness of their hearts, the latter option is usually the one smaller companies prefer, if only for economical reasons.
Of course, putting up jobs on a Web site is just the tip of the iceberg. Once those resumes begin coming in, there’s still the task of organizing them into categories, wading through all the data to short-list candidates for interviews, and filing rejected applications for future use. Oh, yes…and just to make things interesting, there’s usually a clock ticking away in the background as well.
With the help of powerful open-source tools like PHP and mySQL, the process can be simplified considerably. And over the course of this article, I’m going to demonstrate how, by building a job listing system suitable for a small- or medium-sized business.
The goal here is two-fold: to introduce novice and intermediate programmers to the process of designing and implementing a Web-based application; and to offer HR managers and other interested folk a possible solution to their woes.
Onwards!
An Ideal World
Before getting into the nitty-gritty of syntax and structure, it is important to understand the problems I am addressing, so that the functional requirements of the solution become clear. Very briefly, here they are:
-
Every placement call attracts a large number of resumes. Most of these resumes are printed documents, making it harder to search through them for potential candidates. Ideally, resumes should be made available electronically.
-
Resumes are not in a standard format. Different applicants use different templates, styles, and colours, making it harder to read and sort through the volume of data. Ideally, every resume should be in a standard format, with standard fields and some structure imposed on the data within.
-
With a large volume of responses, job applications may get misplaced or destroyed. Ideally, every resume, once entered into the system, should remain stored there with any data loss, so that it may be accessed at any time in the future.
-
Searching through a large number of job applications for specific skills or capabilities is a time-consuming process. Ideally, the resume database should be easily searchable against pre-defined criteria.
Having understood the problems, it becomes easier to decide on the requirements of the solution. An analysis of the problems above reveals that most of them would be resolved if I had a system which:
-
categorized resumes by job;
-
imposed a standard structure on the contents of a resume;
-
processed resumes electronically;
-
incorporated a search engine to easily produce a subset of the database matching specific criteria;
-
archived applications over a period of time;
-
allowed administrators to easily add and remove job listings from the Web site.
This, therefore, constitutes the initial feature set for the application, and serves as the guideline for future development activity.
Entry Point
At this point, I have also come up with a few ideas as to how this system is likely to work.
Conceptually, I can consider this system to be split into two parts: a user section, and an administration section. Here is an initial draft of the basic flow and rules that I plan to build into this application.
-
In the user section, the entry point for a potential job applicant will be a job listing, which will display a list of open positions within the organization. This should, in turn, allow the applicant to select a specific job and obtain detailed information on responsibilities, qualifications, salary and other basic information.
-
The applicant will also have the option to fill up an application form online, and provide the organization with personal information, qualifications, experience and other information typically found in a resume. This data will be stored in a database, accessible and searchable by administrators.
-
In the administration section, administrators will have the option to add, edit and delete job listings.
-
The administration section will also contain a search form, to help administrators search stored applications for specific skills or qualifications.
Going To The Database
Having written down the requirements, it becomes much easier to begin designing the architecture of the system. The first (and most important) part of this design process is database design, in which I will be designing the tables to hold application data.
This is a good time for you to download the source code, so that you can refer to it throughout this article (you will need a Web server capable of running PHP and a mySQL database).
I plan to store job listings in a single “listing” table.
#
# Table structure for table 'listing'
#
DROP TABLE IF EXISTS listing;
CREATE TABLE listing (
jcode varchar(10) NOT NULL,
designation varchar(255) NOT NULL,
responsibilities text NOT NULL,
qualifications text NOT NULL,
cname varchar(255) NOT NULL,
cmail varchar(255) NOT NULL,
posted date DEFAULT '0000-00-00' NOT NULL,
fk_department tinyint(3) unsigned DEFAULT '0' NOT NULL,
fk_location tinyint(3) unsigned DEFAULT '0' NOT NULL,
fk_salary tinyint(3) unsigned DEFAULT '0' NOT NULL,
PRIMARY KEY (jcode),
KEY jcode (jcode)
);
#
# jcode - unique identifier for each job listing
# designation - job designation
# responsibilities - job responsibilities
# qualifications - job qualifications
# cname - name of person posting job
# cmail - email address of person posting job
# fk_department - foreign key - which department is this job in?
# fk_location - foreign key - which city is this job in? (for multi-location organizations)
# fk_salary - foreign key - what is the expected compensation range for this job?
#
Let’s fill this up with a couple of dummy entries.
#
# Dumping data for table 'listing'
#
INSERT INTO listing (jcode, designation, responsibilities, qualifications, cname, cmail, posted, fk_department, fk_location, fk_salary) VALUES ( 'X5436', 'Senior Web Developer', 'Applicant will be responsible for developing Web applications and executing Web-related projects for corporate customers. ', 'Applicant should be familiar with scripting languages (PHP and Perl), databases (mySQL, PostgreSQL). Applicant should be comfortable with both Windows and *NIX operating system. Applicant will also be required to demonstrate a thorough knowledge of software design and engineering principles.', 'Roger Rabbit', '[email protected]', '2001-05-22', '3', '4', '1');
INSERT INTO listing (jcode, designation, responsibilities, qualifications, cname, cmail, posted, fk_department, fk_location, fk_salary) VALUES ( 'KA6547', 'Project Manager', 'Applicant will be responsible for managing projects within the organization. Responsibilities include developing project plans and schedules, tracking project progress, communicating with the customer, and ensuring that deadlines and deliveries are met.', 'Applicant should be familiar with office applications like Word, Excel, Powerpoint and Project. Applicant should have prior experience with project management tasks, and must bring enthusiasm and professionalism to the post.', 'Bugs Bunny', '[email protected]', '2001-04-05', '4', '5', '11');
As you can say, this table references information in three other tables via foreign keys - let’s take a look at those next, together with sample entries for each.
#
# Table structure for table 'location'
#
DROP TABLE IF EXISTS location;
CREATE TABLE location (
id tinyint(3) unsigned NOT NULL auto_increment,
location varchar(255) NOT NULL,
PRIMARY KEY (id)
);
#
# id - unique record identifier
# location - name of city where applicant will be posted
#
# Dumping data for table 'location'
#
INSERT INTO location (id, location) VALUES ( '1', 'New York');
INSERT INTO location (id, location) VALUES ( '2', 'London');
INSERT INTO location (id, location) VALUES ( '3', 'Paris');
INSERT INTO location (id, location) VALUES ( '4', 'Tokyo');
INSERT INTO location (id, location) VALUES ( '5', 'Bombay');
#
# Table structure for table 'department'
#
DROP TABLE IF EXISTS department;
CREATE TABLE department (
id tinyint(3) unsigned NOT NULL auto_increment,
department varchar(255) NOT NULL,
PRIMARY KEY (id)
);
#
# id - unique record identifier
# department - name of department
#
# Dumping data for table 'department'
#
INSERT INTO department (id, department) VALUES ( '1', 'Human Resources');
INSERT INTO department (id, department) VALUES ( '2', 'Accounting');
INSERT INTO department (id, department) VALUES ( '3', 'Engineering');
INSERT INTO department (id, department) VALUES ( '4', 'Design');
INSERT INTO department (id, department) VALUES ( '5', 'Administration');
#
# Table structure for table 'salary'
#
DROP TABLE IF EXISTS salary;
CREATE TABLE salary (
id tinyint(3) unsigned NOT NULL auto_increment,
salary varchar(255) NOT NULL,
PRIMARY KEY (id)
);
#
# id - unique record identifier
# salary - salary range
#
#
# Dumping data for table 'salary'
#
INSERT INTO salary (id, salary) VALUES ( '1', 'Not specified');
INSERT INTO salary (id, salary) VALUES ( '2', '< USD 20,000');
INSERT INTO salary (id, salary) VALUES ( '3', 'USD 20,000-29,900');
INSERT INTO salary (id, salary) VALUES ( '4', 'USD 30,000-39,900');
INSERT INTO salary (id, salary) VALUES ( '5', 'USD 40,000-49,900');
INSERT INTO salary (id, salary) VALUES ( '6', 'USD 50,000-59,900');
INSERT INTO salary (id, salary) VALUES ( '7', 'USD 60,000-69,900');
INSERT INTO salary (id, salary) VALUES ( '8', 'USD 70,000-79,900');
INSERT INTO salary (id, salary) VALUES ( '9', 'USD 80,000-89,900');
INSERT INTO salary (id, salary) VALUES ( '10', 'USD 90,000-99,900');
INSERT INTO salary (id, salary) VALUES ( '11', '> USD 100,000');
These are very simple tables, each having a primary key (id) and a corresponding value. In case you’re wondering why I’ve split these items into separate tables, rather than including them all in the “listing” table, or even hard-coding them into the end application, the reason is very simple: I want to make it easier for an administrator to add and edit these values.
By breaking them into separate tables, an administrator who wants to customize the application (for example, change the number and name of the job locations, or edit the various department names) can do so without having to mess about with the program code. This is part of a process known as “normalization”, and it’s very important when designing a database with two or more tables (links to some good articles on normalization appear at the end of this article)
The Five Rs
The four tables you’ve seen above will enable me to build the pages need to list open jobs, together with their descriptions. However, once the user has decided to apply for a job, the data he enters needs to be stored somewhere. Consequently, I’ve created some tables to store this information.
Note that the information I plan to request in the job application form can broadly be broken down into five sections (personal information, education, work history, skills and references), and so the tables I’ve constructed follow that breakdown too.
#
# Table structure for table 'r_user'
DROP TABLE IF EXISTS r_user;
CREATE TABLE r_user (
rid tinyint(3) unsigned NOT NULL auto_increment,
jcode varchar(10) NOT NULL,
fname varchar(255) NOT NULL,
lname varchar(255) NOT NULL,
dob date DEFAULT '0000-00-00' NOT NULL,
addr1 varchar(255) NOT NULL,
addr2 varchar(255),
city varchar(255) NOT NULL,
state varchar(255) NOT NULL,
zip varchar(10) NOT NULL,
fk_country tinyint(3) unsigned DEFAULT '0' NOT NULL,
phone varchar(25) NOT NULL,
email varchar(255) NOT NULL,
url varchar(255),
relo tinyint(4) DEFAULT '0' NOT NULL,
posted date DEFAULT '0000-00-00' NOT NULL,
PRIMARY KEY (rid),
KEY jcode (jcode),
KEY rid (rid)
);
#
# rid - unique identifier for each application/resume, used to reference it throughout the application
# jcode - job this application is for
# fname - applicant's first name
# lname - applicant's last name
# dob - applicant's date of birth
# addr1 - applicant's address
# add2 - applicant's address
# city - applicant's city
# state- applicant's state
# zip - applicant's zip code
# fk_country - applicant's country; foreign key to "country" table
# phone - applicant's phone number
# email - applicant's email address
# url - applicant's Web site
# relo - whether applicant is willing to relocate
# posted - date application was posted
#
The “r_user” table holds the applicant’s personal information, and contains one record per application; the “rid” field serves as a unique identifier per application.
#
# Table structure for table 'r_education'
#
DROP TABLE IF EXISTS r_education;
CREATE TABLE r_education (
rid tinyint(3) unsigned DEFAULT '0' NOT NULL,
institute varchar(255) NOT NULL,
fk_degree tinyint(3) unsigned DEFAULT '0' NOT NULL,
fk_subject tinyint(3) unsigned DEFAULT '0' NOT NULL,
year year(4) DEFAULT '0000' NOT NULL,
KEY fk_degree (fk_degree),
KEY fk_subject (fk_subject),
KEY rid (rid)
);
#
# rid - which application is this information for?
# institute - name of educational institution
# fk_institute - degree type; foreign key to "degree" table
# fk_subject - degree subject; foreign key to "subject" table
# year - degree obtained in which year?
#
#
# Table structure for table 'r_employment'
#
DROP TABLE IF EXISTS r_employment;
CREATE TABLE r_employment (
rid tinyint(3) unsigned DEFAULT '0' NOT NULL,
employer varchar(255) NOT NULL,
fk_industry tinyint(3) unsigned DEFAULT '0' NOT NULL,
start_year year(4) DEFAULT '0000' NOT NULL,
end_year year(4) DEFAULT '0000' NOT NULL,
responsibilities text NOT NULL,
KEY rid (rid)
);
#
# rid - which application is this information for?
# employer - name of employer
# fk_industry - employer's industry; foreign key to "industry" table
# start_year - started work in...?
# end_year - ended work in...?
# responsibilities - free-form description of job responsibilities at this workplace
#
#
# Table structure for table 'r_skill'
#
DROP TABLE IF EXISTS r_skill;
CREATE TABLE r_skill (
rid tinyint(3) unsigned DEFAULT '0' NOT NULL,
skill varchar(255) NOT NULL,
experience tinyint(3) unsigned DEFAULT '0' NOT NULL,
KEY skill (skill),
KEY experience (experience),
KEY rid (rid)
);
#
# rid - which application is this information for?
# skill - name of skill
# experience - years experience in this skill
#
#
# Table structure for table 'r_reference'
#
DROP TABLE IF EXISTS r_reference;
CREATE TABLE r_reference (
rid tinyint(3) unsigned DEFAULT '0' NOT NULL,
name varchar(255) NOT NULL,
phone varchar(25) NOT NULL,
email varchar(255),
KEY rid (rid)
);
#
# rid - which application is this information for?
# name - reference's name
# phone - reference's phone number
# email - reference's email address
#
The “r_education”, “r_employer”, “r_skills” and “r_reference” tables hold education, work history, skills and references respectively. Note that these tables can contain more than one record per applicant (because an applicant might list several skills or references in a single application), with the records linked to each other via the unique “rid” field.
Lucky Thirteen
Finally, I have some ancillary tables which store country lists, industry lists, subject lists and the like. Again, these have been placed in separate tables and linked via foreign keys to simplify customizing the application for diverse needs. Here they are:
#
# Table structure for table 'country'
#
DROP TABLE IF EXISTS country;
CREATE TABLE country (
id tinyint(4) unsigned NOT NULL auto_increment,
country varchar(255) NOT NULL,
PRIMARY KEY (id)
);
#
# id - unique record identifier
# country - country name
#
#
# Dumping data for table 'country'
#
INSERT INTO country (id, country) VALUES ( '', 'Afghanistan');
INSERT INTO country (id, country) VALUES ( '', 'Albania');
#
# Table structure for table 'degree'
#
DROP TABLE IF EXISTS degree;
CREATE TABLE degree (
id tinyint(3) unsigned NOT NULL auto_increment,
degree varchar(255) NOT NULL,
PRIMARY KEY (id)
);
#
# id - unique record identifier
# degree - degree type
#
#
# Dumping data for table 'degree'
#
INSERT INTO degree (id, degree) VALUES ( '1', 'High School degree');
INSERT INTO degree (id, degree) VALUES ( '2', 'Undergraduate degree');
INSERT INTO degree (id, degree) VALUES ( '3', 'Bachelor\'s degree');
#
# Table structure for table 'industry'
#
DROP TABLE IF EXISTS industry;
CREATE TABLE industry (
id tinyint(4) unsigned NOT NULL auto_increment,
industry varchar(255) NOT NULL,
PRIMARY KEY (id)
);
#
# id - unique record identifier
# industry - industry type
#
#
# Dumping data for table 'industry'
#
INSERT INTO industry (id, industry) VALUES ( '1', 'Advertising');
INSERT INTO industry (id, industry) VALUES ( '2', 'Agriculture and Forestry');
#
# Table structure for table 'subject'
#
DROP TABLE IF EXISTS subject;
CREATE TABLE subject (
id tinyint(3) unsigned NOT NULL auto_increment,
subject varchar(255) NOT NULL,
PRIMARY KEY (id)
);
#
# id - unique record identifier
# subject - subject name
#
#
# Dumping data for table 'subject'
#
INSERT INTO subject (id, subject) VALUES ( '', 'Accounting');
INSERT INTO subject (id, subject) VALUES ( '', 'Actuarial Science');
The “jobs.sql” file in the source code archive contains a longer list of items for these ancillary tables.
How many tables is that? Well, Joe, it’s lucky number thirteen! Whoopee!
Building The Foundation
With the database designed and out of the way, it’s time to start writing some code to interface with it. The first script, “job_list.php”, is the entry point to the application, and simply displays a list of available jobs, classified by department.
<?php
// job_list.php - display list of open jobs
// includes
include("config.php");
include("functions.php");
?>
<html>
<head>
<basefont face="Verdana" size="2">
</head>
<body bgcolor=white>
<?php $image="listings.jpg"; ?>
<?php include("header.inc.php"); ?>
<?php
// generate list
?>
<?php include("footer.inc.php"); ?>
</body>
</html>
Each page generated through this application has a particular layout - a blue banner at the top, a logo at the right, and a title (actually an image). The bottom of every page has a copyright notice and a disclaimer. Since these elements will remain constant, through the application, I’ve placed the corresponding HTML code in separate header and footer files, and simply include()d them on each page.
Again, by separating common interface elements into separate files, I’ve made it easier to customize the look of the application; simply alter these files, and the changes will be reflected on all the pages.
The variable $image stores the name of the image title for each page, and is used by “header.inc.php” - as you can see.
<!-- appears at the top of every page -->
<table bgcolor="6583C3" width="100%" cellspacing="0" cellpadding="0">
<tr>
<td height=50 align=right> <img src="images/header.gif" alt="" border="0"></td>
</tr>
</table>
<p>
<img src="images/<?php echo $image; ?>" alt="" border="0">
<p>
<!-- end of header.inc -->
In addition, two other files, “config.php” and “functions.php” are also included at the top of every page - they store database access information and useful functions, respectively.
<?php
// config.php - useful variables
// database parameters
// alter this as per your configuration
$database="jobs";
$user = "root";
$pass = "";
$hostname = "localhost";
?>
Back to “job_list.php” - here’s the code that takes care of connecting to the database and actually generating the job listing.
<?php include("header.inc.php"); ?>
<?php
// open connection to database
$connection = mysql_connect($hostname, $user, $pass) or die ("Unable to connect!");
// get list of departments with open jobs
$query = "SELECT DISTINCT id, department from department, listing WHERE department.id = listing.fk_department";
$result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query. " . mysql_error());
// iterate through resultset
while(list($id, $department) = mysql_fetch_row($result))
{
// print department name
echo "<b>Department:</b> $department";
// look for jobs within the department and print as list
$query2 = "SELECT jcode, designation from listing WHERE listing.fk_department = '$id'";
$result2 = mysql_db_query($database, $query2, $connection) or die ("Error in query: $query2. " . mysql_error());
echo "<ul>";
while(list($jcode, $dsg) = mysql_fetch_row($result2))
{
echo "<li><a href=job_details.php?jcode=$jcode>$dsg ($jcode)</a>";
}
echo "</ul>";
echo "<p>";
}
// clean up
mysql_close($connection);
?>
<?php include("footer.inc.php"); ?>
In this case, I’ve first queried the “listing” table for a list of departments holding open jobs - note the DISTINCT keyword to eliminate duplicate entries. Then, for each of those departments, I’ve printed the job designation and job code, and linked it to a script which will display further information.
Here’s what it looks like:
The Devil Is In The Details
The script “job_details.php” is designed to accept a particular job code, connect to the database, and print details such as qualifications and responsibilities for that job. It also includes a link to the job application form, should the user be interested in applying for the job.
<?php
// job_details.php - display job details
// includes
// check for missing parameters
if (!$jcode || $jcode == "")
{
header("Location:error.php");
exit;
}
// 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 listing.designation, listing.jcode, department.department, location.location, salary.salary, listing.responsibilities, listing.qualifications, listing.cname, listing.cmail, listing.posted from department, listing, location, salary WHERE department.id = listing.fk_department AND location.id = listing.fk_location AND salary.id = listing.fk_salary AND listing.jcode = '$jcode'";
$result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query. " . mysql_error());
// error check
if (mysql_num_rows($result) <= 0)
{
header("Location:error.php");
exit;
}
else
{
// obtain data from resultset
list($designation, $jcode, $department, $location, $salary, $description, $qualification, $cname, $cmail, $posted) = mysql_fetch_row($result);
// clean up
mysql_close($connection);
?>
<!-- snip -->
<!-- print job details -->
<b>Designation:</b> <?php echo $designation; ?>
<p>
<b>Department:</b> <?php echo $department; ?>
<p>
<!-- snip -->
<b>Posted on:</b> <?php echo fixDate($posted); ?>
<p>
<!-- link to application form -->
<a href="apply.php?jcode=<?php echo $jcode; ?>">Apply online</a> for this job, or <a href="job_list.php">return to job listings</a>
<!-- snip -->
<?php
}
?>
The first thing this script does is check to ensure that it has been passed a job code, via the URL GET method. If this job code is absent, control is transferred to the generic error handler via HTTP redirection and the header() function.
Assuming a job code is present, the next thing to do is ensure that it is valid, and that there does exist such a job in the database. A query is executed to obtain a complete job description (by joining the “listing” table to other ancillary tables via foreign keys). If the query returns a value, the information is printed; if not, the error handler is invoked again.
The list() function is used to separate the various elements of the returned row and assign them to regular variables; these are then printed in the appropriate places. At the end, a link to the “apply.php” script takes the user to the application form, again using the job code as identifier.
Note the fixDate() function - it is used to turn mySQL’s DATE type into something a little more readable, and is read from “functions.php”.
<?php
// function to format mySQL DATE values
function fixDate($val)
{
//split it up into components
$arr = explode(" ", $val);
$datearr = explode("-", $arr[0]);
// create a timestamp with mktime(), format it with date()
return date("d M Y", mktime(0, 0, 0, $datearr[1], $datearr[2], $datearr[0]));
}
?>
Here’s what it looks like:
Applying Yourself
The job application form, “apply.php”, and the corresponding data processor, “apply_rslt.php”, form the core of this application. They are responsible for generating raw data and storing it appropriately in the database; consequently, special care has to be taken when developing them.
Like the previous script, “apply.php” must first check to ensure that it has received a valid job code.
<?php
// apply.php - generate application form
// 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());
// error check
if (mysql_num_rows($result) <= 0)
{
header("Location:error.php");
exit;
}
else
{
// obtain data from resultset
list($designation, $jcode, $department) = mysql_fetch_row($result);
mysql_free_result($result);
?>
The remainder of the script is a regular form; it includes both text fields and drop-down selection lists, many of which are generated from the ancillary tables discussed earlier. Take a look at the personal information section.
<table border="0" cellspacing="5" cellpadding="2">
<form action="apply_rslt.php" method="post">
<input type="hidden" name="jcode" value="<?php echo $jcode; ?>"
<!-- personal information section -->
<tr>
<td colspan=4><img src="images/pi.gif"></td>
</tr>
<tr>
<td colspan=2>First name<font color="red">*</font></td>
<td colspan=2>Last name<font color="red">*</font></td>
</tr>
<tr>
<td colspan=2><input type="text" name="fname" size="20" maxlength="255"></td>
<td colspan=2><input type="text" name="lname" size="20" maxlength="255"></td>
</tr>
<!-- snip -->
<tr>
<td colspan=4>Country<font color="red">*</font></td>
</tr>
<tr>
<td colspan=4><select name="country">
<?php
// get country list
$query = "SELECT id, country from country";
$result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query. " . mysql_error());
while (list($id, $country) = mysql_fetch_row($result))
{
echo "<option value=$id>$country</option>";
}
mysql_free_result($result);
?>
</select></td>
</tr>
<!-- snip -->
<tr>
<td colspan=4>Date of birth<font color="red">*</font><br><font size="-2">(in dd-mm-yyyy format)</font></td>
</tr>
<tr>
<td colspan=4>
<select name="dd">
<?php for ($x=1; $x<=31; $x++) { echo "<option value=\"" . sprintf("%02d", $x) . "\">" . sprintf("%02d", $x) . "</option>"; } ?>
</select> -
<select name="mm">
<?php for ($x=1; $x<=12; $x++) { echo "<option value=\"" . sprintf("%02d", $x) . "\">" . sprintf("%02d", $x) . "</option>"; } ?>
</select> -
<select name="yyyy">
<!-- display from 1940 to (current year-10) -->
<?php for ($x=1940; $x<=(date("Y", mktime())-10); $x++) { echo "<option value=$x>$x</option>"; } ?>
</select>
</td>
</tr>
As you can see, the country selection list is generated from the “country” table in the database, while the date-of-birth drop-down lists are generated with “for” loops.
The education section uses the “degree” and “subject” tables to build a list of possible educational qualifications.
<!-- education section -->
<tr>
<td colspan=4><img src="images/ed.gif"></td>
</tr>
<tr>
<td colspan=4><i>You may fill all or none of the rows below; ensure that no fields are left empty per filled-in row</i></td>
</tr>
<tr>
<td>Institute/University<br><font size=-2>(example: XYZ University)</td>
<td>Degree<br><font size=-2>(example: Master's degree)</td>
<td>Primary subject<br><font size=-2>(example: Accounting)</td>
<td>Year<br><font size=-2>(example: 1992)</td>
</tr>
<?php
// get degree list
$query = "SELECT id, degree from degree";
$degree_result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query. " . mysql_error());
// get subject list
$query = "SELECT id, subject from subject";
$subject_result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query. " . mysql_error());
for ($x=0; $x<5; $x++)
{
?>
<tr>
<td><input type="text" name="institute[]" size="20" maxlength="255"></td>
<td><select name="degree[]">
<?php
while (list($id, $degree) = mysql_fetch_row($degree_result))
{
echo "<option value=$id>$degree</option>";
}
// same data, no need to query again
mysql_data_seek($degree_result, 0);
?>
</select></td>
<td><select name="subject[]">
<?php
while (list($id, $subject) = mysql_fetch_row($subject_result))
{
echo "<option value=$id>$subject</option>";
}
// same data, no need to query again
mysql_data_seek($subject_result, 0);
?>
</select></td>
<td><input type="text" name="degree_year[]" size="4" maxlength="4"></td>
</tr>
<?php
}
mysql_free_result($degree_result);
mysql_free_result($subject_result);
?>
As you can see, the code above will generate five rows, for the applicant to enter up to five different qualifications. You will notice that though the degree and subject drop-downs are generated five times, the query for each is performed only once.
I’ve put the query outside the “for” loop for a simple reason: performance. There is no reason to perform the same query five times over to obtain the same data, as it adds to the overhead on the server. A far preferable option is to perform the query once, store the result set, and use the mysql_data_seek() function to iterate through it as many times as necessary. It’s a little thing, but worth noting for its impact on the overall performance of the applications.
The employment history section uses the “industry” table to generate an industry list;
<!-- employment history -->
<tr>
<td colspan=4><img src="images/em.gif"></td>
</tr>
<tr>
<td colspan=4><i>You may fill all or none of the sections below; ensure that no fields are left empty per filled-in section</i></td>
</tr>
<?php
// get industry list
$query = "SELECT id, industry from industry";
$ind_result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query. " . mysql_error());
for ($x=0; $x<3; $x++)
{
// if first time, print example
?>
<tr>
<td>Employer [<?php echo ($x+1); ?>]
<?php if ($x == 0) { echo "<br><font size=-2>(example: ABC, Inc.)</font>"; } ?>
</td>
<td>Industry
<?php if ($x == 0) { echo "<br><font size=-2>(example: Advertising)</font>"; } ?></td>
<td>Start year
<?php if ($x == 0) { echo "<br><font size=-2>(example: 1996)</font>"; } ?>
</td>
<td>End year
<?php if ($x == 0) { echo "<br><font size=-2>(example: 1998)</font>"; } ?>
</td>
</tr>
<tr>
<td><input type="text" name="employer[]" size="15" maxlength="255"></td>
<td><select name="industry[]">
<?php
// print industry list
while (list($id, $industry) = mysql_fetch_row($ind_result))
{
echo "<option value=$id>$industry</option>";
}
// resultset pointer back to zero
mysql_data_seek($ind_result, 0);
?>
</select></td>
<td><input type="text" name="start_year[]" size="4" maxlength="4"></td>
<td><input type="text" name="end_year[]" size="4" maxlength="4"></td>
</tr>
<tr>
<td colspan=4>Responsibilities
<?php if ($x == 0) { echo "<br><font size=-2>(example: Managing projects and...)"; }?>
</td>
</tr>
<tr>
<td colspan=4><textarea name="rsp[]" cols="40" rows="8"></textarea></td>
</tr>
<?php
}
mysql_free_result($ind_result);
mysql_close($connection);
?>
In this case, the applicant can enter up to three previous jobs; however, since I want the illustrative example to be printed only once, I’ve incorporated a small “if” statement within the “for” loop to check for the first iteration of the loop.
As stated earlier, for items such as employment history and skills, the user may enter multiple records; each of these records is stored as a single row in the appropriate table, with the set linked to each other via the “rid” resume identifier. Note also that many of the multiple-record items in the form above are being passed as arrays, rather than ordinary variables; this makes it easier to check them for invalid data.
It should be noted that I’ve implemented the skills section as a series of text fields, thereby allowing the user the freedom to enter anything (s)he likes. An alternative approach here would be to compile an exhaustive list of skills, as a drop-down list, and allow the user to select from the list. The approach you adopt here will impact the search queries you write for the search engine; I’ll discuss the issue in a little more depth when we get there.
Here’s what the finished product looks like.
Testing Times
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.
<?php
// 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.
<?php
// 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.
<?php
// 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.)
<?php
// 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.
<?php
// 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.
Filing It All Away
At the end of all the validation, the size of the $errorList array is checked. If the size is 0, it implies that no errors were detected, and database insertion begins.
<?php
// no errors
if (sizeof($errorList) == 0)
{
// insert personal info
$query = "INSERT INTO r_user (jcode, fname, lname, dob, addr1, addr2, city, state, zip, fk_country, phone, email, url, relo, posted) VALUES ('$jcode', '$fname', '$lname', '$dob', '$addr1', '$addr2', '$city', '$state', '$zip', '$country', '$phone', '$email', '$url', '$relo', NOW(''))";
$result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query. " . mysql_error());
// get resume id, for use in subsequent operations
$rid = mysql_insert_id($connection);
// insert educational qualifications
for($x=0; $x<sizeof($institute); $x++)
{
if (!empty($institute[$x]) && !empty($degree_year[$x]))
{
$query = "INSERT INTO r_education (rid, institute, fk_degree, fk_subject, year) VALUES ('$rid', '$institute[$x]', '$degree[$x]', '$subject[$x]', '$degree_year[$x]')";
$result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query. " . mysql_error());
}
}
// and so on
// print success code
echo "Your application has been accepted.<p><a href=job_list.php>Return to job listings</a>";
}
else
{
// or list errors
listErrors();
}
?>
If error messages are present, the listErrors() function is called to display a list of error messages. No database insertion takes place, and the user has the option to return to the previous page to rectify the errors.
<?php
// produce a list of errors after validating a form
function listErrors()
{
// read the errorList array
global $errorList;
// print as list
echo "The following errors were encountered: <br>";
echo "<ul>";
for ($x=0; $x<sizeof($errorList); $x++)
{
echo "<li>$errorList[$x]";
}
echo "</ul>";
// link to go back and correct errors
echo "Click <a href=javascript:history.back();>here</a> to go back to the previous page and correct the errors";
}
?>
At this point, all relevant user information has been stored in the various database tables. This is the end of the user process flow; the focus now shifts to data retrieval and maintenance, both of which are exclusively administrative functions.
In the second part of this article, I’ll examine the administration scripts related to adding, editing and deleting job listings, together with a look at a basic search engine to sift through all the data. In the meanwhile, download the code, play with it, send me your thoughts/flames/savings…and come back next time for more!
Note: All examples in this article have been tested on Linux/i586 with Apache 1.3.12, mySQL 3.23 and PHP 4.02. Examples are illustrative only, and are not meant for a production environment. YMMV!
This article was first published on 28 Jun 2001.