Login

The Perfect Job (part 2)

In the first part of this article, you built the architecture
necessary to accept and store resumes online. In this concluding part, find
out how to make use of the stored data to find suitable candidates for a
particular job, and also read about the functions available to maintain and
update the job listings.In the first part of this article, I explained some of the problems typically
associated with data management in a HR department, and put together a
functional specification for a Web-based application to make the task easier.
After putting together a basic database schema and normalizing it, I proceeded
to develop scripts to display job listings, accept user applications, and store
these applications in the database.

While the first part of this article
described the user experience, I have not yet addressed the issues of updating
the job board with new information, removing existing entries, or searching for
potential candidates – all of which formed part of the initial feature set of
this application. And so, in this concluding article, I’m going to wrap things
up by looking at some of the tasks an administrator would need to accomplish in
such a system, and developing some simple scripts to accomplish these.

This article
copyright Melonfire 2001. All rights
reserved.{mospagebreak title=Administrator Ahoy!} If you remember, when I
first put together the “listings” table, I added a couple of dummy entries to
it, just to get things rolling.

Now, that’s fine during the early stages
of application development…but you can’t expect your customers to pop open a
mySQL prompt every time they need to update a job listing. For one thing, they
may not have the technical acumen necessary to manipulate a mySQL database; for
another, they may prefer a pretty GUI to an ugly command-line. Either way, as
the developer, you’re on the hook to package those functions into your
application as well.

Consequently, I’ll begin by putting together an
entry point for administrators, in much the same way as I did for users. This
page will serve as a menu to the different administration functions
available.

As
you can see, it’s almost identical to the user entry point, with the exception
of the edit and delete links next to each job. Each of these calls a script to
perform the appropriate function. The bottom of the page also includes links to
add a new listing, or search the database for potential candidates.

It
should be noted at this point that access to all these administration scripts
should be restricted to privileged users only. The easiest way to do this is to
move them into a separate directory, and protect it using Web-based
authentication.

This article copyright Melonfire
2001. All rights reserved.{mospagebreak title=Adding To The Mix} The
script “add.php” display a form which allows an administrator to add a new job
listing to the system. The script is divided into two sections – one section
displays a form, while the other section processes the data entered into it. The
$submit variable is used to decide which section of the script to execute.

Nothing
too complicated here – this is simply a form, with text fields for some elements
of the job listing, and drop-down boxes for the remainder. You will notice that
the items in the drop-downs are generated from the database; you probably
remember this from last time.

Notice also the ACTION attribute of the
<FORM> tag, which is pointing to a PHP variable called $PHP_SELF. This is
a built-in PHP variable which always holds the name of the currently-executing
script; by including it here, I am ensuring that the same script is also called
to process the form.

Once the form is submitted, the same script is
called again; however, since the $submit variable will now exist, the second
half of the script springs into action.

The error-checking mechanism used here should be familiar to
you from the job application form in the previous article…although the
validation routines here are a little simpler, since this form is far less
complex.

Once the data has been validated and found to be acceptable, an
INSERT query takes care of saving the data to the “listing” table, so that it
immediately appears on the job listing page.

This article copyright Melonfire 2001. All rights
reserved.{mospagebreak title=Changing Things Around} The “edit.php”
script is almost identical; it accepts a job listing, connects to the database,
retrieves the record, and displays a form with the values filled in. The
administrator can now update the listing and save it back to the database.

Just as I have these scripts to alter the “listing” table,
you can develop additional scripts to modify the other ancillary tables –
“country”, “salary” et al – if you like. That said, it should be noted that not
all the tables will change on a regular basis (for example, how often are you
likely to update the list of countries?) Some tables will be set up with an
initial set of records, and will remain unchanged for long periods of time,
while others may change on a weekly basis; as the developer, it’s up to you to
anticipate the likely requirements of the customer, and develop administration
modules appropriately.

This article copyright Melonfire
2001. All rights reserved.{mospagebreak title=Desperately Seeking Perl
Guru} Next up, the search function. This is probably the most-used function in
this type of system, since it allows administrators to quickly extract a set of
applications which match pre-defined criteria. As before, the script has two
sections, one for the form and the other for the processor.

I’ve
implemented two different levels of search here. The first level simply displays
all applications for a specific post, as selected from a drop-down list. This
comes in very handy when you need to count the number of applications received
in response to a particular listing, or aren’t too sure what you’re looking for
and just need to eyeball a bunch of resumes.

This list can be further
refined by specifying the type of educational qualifications and/or the skills
and experience the candidate should have. While the educational qualifications
(degrees and subjects) can be selected from a list, the skills may be entered
into a free-form text field, separated by whitespace. The administrator also has
the option of specifying the level of expertise required, with a set of
comparison operators.

This article copyright Melonfire
2001. All rights reserved.{mospagebreak title=Building Blocks} Once the
form has been submitted, a basic query is generated to return a list of
applications for the specific job. Then, depending on the criteria selected,
that basic query is further modified with AND clauses to refine the list of
results.

Pay special attention to the section which sets up the query
for skills. Since the skills field is a whitespace-separated list of values,
it’s necessary to first split up the list, and create an SQL clause for each
value. The clauses are then joined together with an AND conjunction, to ensure
that the resultset includes *all* the skills specified (you can change this to
OR if you’d like *any* of the skills instead.)

The end result of this
will be a list of names and email addresses, linked to a script which displays
detailed information for that candidate.

Of course, this is only one
option for the search engine. There are numerous possible configurations, and
you may need to modify this as per your specific requirements.

One of the
obvious flaws in this one, for instance, is that it assumes the same level of
expertise for every skill selected – there’s no way I can look for candidates
with three years experience in Perl and five years experience in C++. In order
to accomplish this, I would need to create multiple skill-experience field
pairs, and query against each of them.

It should be noted also that there
is an alternative approach to this keyword-search technique. Currently, my
application form allows applicants to enter skills in whatever format they
desire (each skill is a text field, not a list.) If, instead, I had an
exhaustive list of skills available, I could have the candidates simply select
from a list box, adding an extra level of integrity to the data being collected.
This would also make the search process more efficient, by doing away with the
wildcards and LIKE clauses in the queries above.

The downside of this
approach is that, since you are restricting the candidate to a pre-defined list
of job skills, the list must be exhaustive enough to cover all possibilities.
Since this seemed difficult, I decided not to adopt this approach, and instead
went with the free-form approach…but if it works for you, you might want to
consider it.

This article copyright Melonfire
2001. All rights reserved.{mospagebreak title=The Devil Is In The
Details} The “resume_details.php” script represents the end point of the
administrator experience. Its purpose is to compile all the information stored
about a specific candidate in the various tables into a composite data sheet,
and display this in a structured format.

Nothing too complex here – there are five main sections, and
this script queries the corresponding five tables to build a data sheet
containing the candidate’s personal information, employment history, educational
qualifications, references and skills – in effect, doing the reverse of the
“apply_rslt.php” script. This data sheet has all the information an HR manager
needs to get in touch with a candidate and begin the recruitment process.

This article
copyright Melonfire 2001. All rights
reserved.{mospagebreak title=Handling The Gray Areas} The last script –
and the simplest – is the error handler, “error.php”. If you look at the source
code, you’ll notice many links to this script; very simply, “error.php”
intercepts the error and converts it to a human-readable error message, which is
then displayed to the user.

This article copyright Melonfire
2001. All rights reserved.{mospagebreak title=Endgame} And that just
about concludes this case study. Throughout this development effort, I have made
widespread use of database normalization techniques, PHP’s built-in functions,
HTTP headers, and mySQL database queries. If you are new to PHP, I hope that the
effort has been instructive, and that it has helped you gain a greater
understanding of these powerful open-source tools.

If you’d like to learn
more about some of the issues described throughout the course of this article,
here are a few links:

I
believe that a tool such as the one described over the preceding pages offers
tremendous benefits to any organization in its recruitment efforts. By obtaining
and storing information in electronic format, it reduces paperwork; by imposing
a structure on user information, it makes it easier and quicker to locate
information; and by using a database, it ensures that data does not get
corrupted.

It should be noted also that this is an initial release of the
application, and I expect it to evolve further, with new features being added
and old features being upgraded. It’s always a good idea to review both design
and code as the application evolves – I plan to do this a little further down
the road, and to make changes to both the database schema and the scripts
themselves. This process should take place in conjunction with the development
plan for new features, so that the addition of new features does not add to
overhead and cause performance degradation.

Until then, though, I’m going
to kick back with a cool drink and a good book.