The Perfect Job (part 2)

Complete the job listing system by adding a search engine and administration module.

Unfinished Business

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.

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.

<?php
// admin.php - admin functions entry point

// includes

// 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());

// generate a table
echo "<table border=0 cellspacing=5 cellpadding=5>";

// iterate through resultset
while (list($id, $department) = mysql_fetch_row($result)) {

    // print department name
    echo "<tr><td colspan=4><b>Department:</b> $department</td></tr>";

    // look for jobs within the department and print as list, add edit and delete links
    $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());

    while (list($jcode, $dsg) = mysql_fetch_row($result2)) {
        echo "<tr><td width=10>&nbsp;</td><td>$dsg ($jcode)</td> <td><a href=edit.php?jcode=$jcode><font size=-1>edit</font></a></td> <td><a href=delete.php?jcode=$jcode><font size=-1>delete</font></a></td></tr>";
    }
}
echo "</table>";

?>
<!-- snip -->
<a href="add.php">Add a new listing</a> or <a href="search.php">search the database</a>
<!-- snip -->

Here's what it looks like.

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.

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.

<?php
// form not yet submitted
if (!$submit) {
    // open connection to database
$connection = mysql_connect($hostname, $user, $pass) or die("Unable to connect!"); ?>

<table border="0" cellspacing="5" cellpadding="2">
<form action="<?php echo $PHP_SELF; ?>" method="POST">
<!-- input job details -->

<tr>
<td>Job code<font color="red">*</font></td>
</tr>

<tr>
<td><input type="text" name="jcode" size="10" maxlength="10"></td>
</tr>

<tr>
<td>Designation<font color="red">*</font></td>
<td width=30>&nbsp;</td>
<td>Department<font color="red">*</font></td>
</tr>

<tr>
<td><input type="text" name="dsg" size="25"></td>
<td width=30>&nbsp;</td>
<td><select name="dpt">
<?php
// get department list
$query = "SELECT id, department from department";
    $result = mysql_db_query($database, $query, $connection) or die("Error in query: $query. " . mysql_error());
    while (list($id, $department) = mysql_fetch_row($result)) {
        echo "<option value=$id>$department</option>";
    }
    mysql_free_result($result); ?>
</select></td>
</tr>

<tr>
<td>Location<font color="red">*</font></td>
<td width=30>&nbsp;</td>
<td>Salary<font color="red">*</font></td>
</tr>

<tr>
<td><select name="loc">
<?php
// get location list
$query = "SELECT id, location from location";
    $result = mysql_db_query($database, $query, $connection) or die("Error in query: $query. " . mysql_error());
    while (list($id, $location) = mysql_fetch_row($result)) {
        echo "<option value=$id>$location</option>";
    }
    mysql_free_result($result); ?>
</select></td>
<td width=30>&nbsp;</td>
<td><select name="sal">
<?php
// get salary list
$query = "SELECT id, salary from salary";
    $result = mysql_db_query($database, $query, $connection) or die("Error in query: $query. " . mysql_error());
    while (list($id, $salary) = mysql_fetch_row($result)) {
        echo "<option value=$id>$salary</option>";
    }
    mysql_free_result($result);

    mysql_close($connection); ?>
</select></td>
</tr>

<tr>
<td>Responsibilities<font color="red">*</font></td>
<td width=30>&nbsp;</td>
<td>Qualifications<font color="red">*</font></td>
</tr>

<tr>
<td><textarea name="rsp" cols="40" rows="8"></textarea></td>
<td width=30>&nbsp;</td>
<td><textarea name="qlf" cols="40" rows="8"></textarea></td>
</tr>

<tr>
<td>Contact person<font color="red">*</font></td>
<td width=30>&nbsp;</td>
<td>Email address<font color="red">*</font></td>
</tr>

<tr>
<td><input type="text" name="cname" size="25"></td>
<td width=30>&nbsp;</td>
<td><input type="text" name="cmail" size="25"></td>
</tr>

<tr>
<td align=center colspan=3><input type=submit name=submit value="Add Listing"></td>
</tr>

</table>
</form>
<?php
} else {
    // form submitted, process it
}
?>

Here's what it looks like.

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.

<?php
// form not yet submitted
if (!$submit) {
    // generate form
} else {
    // form submitted, process it

    // set up error list array
    $errorList = array();
    $count = 0;

    // validate text input fields
    if (empty($jcode)) {
        $errorList[$count] = "Invalid entry: Job code";
        $count++;
    }

    if (empty($dsg)) {
        $errorList[$count] = "Invalid entry: Designation";
        $count++;
    }

    if (empty($rsp)) {
        $errorList[$count] = "Invalid entry: Responsibilities";
        $count++;
    }

    if (empty($qlf)) {
        $errorList[$count] = "Invalid entry: Qualifications";
        $count++;
    }

    if (empty($cname)) {
        $errorList[$count] = "Invalid entry: Contact name";
        $count++;
    }

    if (empty($cmail) || isEmailInvalid($cmail)) {
        $errorList[$count] = "Invalid entry: Email address";
        $count++;
    }

    if (sizeof($errorList) == 0) {
        // open connection to database
        $connection = mysql_connect($hostname, $user, $pass) or die("Unable to connect!");

        // insert data
        $query = "INSERT INTO listing (jcode, designation, responsibilities, qualifications, cname, cmail, posted, fk_department, fk_location, fk_salary) VALUES ('$jcode', '$dsg', '$rsp', '$qlf', '$cname', '$cmail', NOW(), '$dpt', '$loc', '$sal')";
        $result = mysql_db_query($database, $query, $connection) or die("Error in query: $query. " . mysql_error());

        // clean up
        mysql_close($connection);
        echo "Entry successfully added.<p><a href=$PHP_SELF>Add another entry</a>, or <a href=job_list.php>return to job listings</a>";
    } else {
        listErrors();
    }
}
?>

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.

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.

<?php
// edit.php - edit job listing

// form not yet submitted
if (!$submit) {
    // open connection to database
    $connection = mysql_connect($hostname, $user, $pass) or die("Unable to connect!");

    // get job details
    $query = "SELECT designation, jcode, fk_department, fk_location, fk_salary, responsibilities, qualifications, cname, cmail from listing WHERE jcode = '$jcode'";

    // snip

    list($designation, $jcode, $department, $location, $salary, $description, $qualification, $cname, $cmail, $posted) = mysql_fetch_row($result);

    // display form with values pre-filled
?>

<!-- snip -->

<table border="0" cellspacing="5" cellpadding="2">
<form action="<?php echo $PHP_SELF; ?>" method="POST">
<input type=hidden name="jcode" value="<?php echo $jcode; ?>">
<!--  job details -->

<tr>
<td>Designation<font color="red">*</font></td>
<td width=30>&nbsp;</td>
<td>Department<font color="red">*</font></td>
</tr>

<tr>
<td><input type="text" name="dsg" size="25" value="<?php echo $designation; ?>"></td>
<td width=30>&nbsp;</td>
<td><select name="dpt">
    <?php
    // get department list
    $query = "SELECT id, department from department";
    $result = mysql_db_query($database, $query, $connection) or die("Error in query: $query. " . mysql_error());
    while (list($id, $dpt) = mysql_fetch_row($result)) {
        echo "<option value=$id";

        // pre-select value
        if ($id == $department) {
            echo " selected";
        }

        echo ">$dpt</option>";
    }
    mysql_free_result($result); ?>
</select></td>
</tr>

<!-- and so on -->

<tr>
<td align=center colspan=3><input type=submit name=submit value="Update Listing"></td>
</tr>

</table>
</form>

<?php
} else {
    // form submitted, process
}
?>

Once the form has been submitted, an UPDATE query is executed to update the database with the new information.

<?php
// form not yet submitted
if (!$submit) {
    // generate form
}
// form submitted, process
else {
    // set up error list array
    $errorList = array();
    $count = 0;

    // validate text input fields

    if (sizeof($errorList) == 0) {
        // no errors
        // open connection to database
        $connection = mysql_connect($hostname, $user, $pass) or die("Unable to connect!");

        // update data
        $query = "UPDATE listing SET designation='$dsg', responsibilities='$rsp', qualifications='$qlf', cname='$cname', cmail='$cmail', fk_department='$dpt', fk_location='$loc', fk_salary='$sal' WHERE jcode='$jcode'";
        $result = mysql_db_query($database, $query, $connection) or die("Error in query: $query. " . mysql_error());

        // clean up
        mysql_close($connection);

        // redirect
        header("Location:admin.php");
    } else {
        // errors, display
        listErrors();
    }
}
?>

The "delete.php" script is the simplest of the lot. It accepts a job code, connects to the database, DELETEs the appropriate records, and redirects the browser back to the menu.

<?php
// delete.php - delete job listing

// includes and error checks

// open connection to database
$connection = mysql_connect($hostname, $user, $pass) or die("Unable to connect!");

// delete record
$query = "DELETE FROM listing WHERE jcode = '$jcode'";
$result = mysql_db_query($database, $query, $connection) or die("Error in query: $query. " . mysql_error());
mysql_close($connection);

// redirect
header("Location:admin.php");
?>

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.

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.

<?php
// search.php - search for specific applications

// includes

// open connection to database
$connection = mysql_connect($hostname, $user, $pass) or die("Unable to connect!");

if (!$submit) {
    // form not yet submitted, display form
?>
<html>
<head>
<basefont face="Verdana" size="2">
</head>

<body bgcolor=white>

<?php $image="search.jpg"; ?>
<?php include("header.inc.php"); ?>

<form action="<?php echo $PHP_SELF; ?>" method="post">
Display all applications for the post
<select name="jcode">
<?php
// get list of open jobs
$query = "SELECT DISTINCT jcode, designation from listing";
    $result = mysql_db_query($database, $query, $connection) or die("Error in query: $query. " . mysql_error());

    // and print
    while (list($jcode, $designation) = mysql_fetch_row($result)) {
        echo "<option value=$jcode>$designation ($jcode)</option>";
    }
    mysql_free_result($result); ?>
</select>
<p>
<ul>
<li>with skills matching the keywords
<input type=text name=skills size=35>
<p>
and experience
<select name=exp_modifier>
<option value="">&lt;unspecified&gt;</option>
<option value="=">equal to</option>
<option value=">=">greater than or equal to</option>
<option value="<=">less than or equal to</option>
</select>
<input type=text name=years size=2 maxlength=2>
years
<p>
<li>with educational qualifications equivalent to
<select name="degree">
<option value="">&lt;unspecified&gt;</option>
<?php
// get list of degrees
$query = "SELECT id, degree from degree";
    $result = mysql_db_query($database, $query, $connection) or die("Error in query: $query. " . mysql_error());
    while (list($id, $degree) = mysql_fetch_row($result)) {
        echo "<option value=$id>$degree</option>";
    }
    mysql_free_result($result); ?>
</select>
in
<select name=subject>
<option value="">&lt;unspecified&gt;</option>
<?php
// get list of subjects
$query = "SELECT id, subject from subject";
    $result = mysql_db_query($database, $query, $connection) or die("Error in query: $query. " . mysql_error());
    while (list($id, $subject) = mysql_fetch_row($result)) {
        echo "<option value=$id>$subject</option>";
    }
    mysql_free_result($result); ?>
</select>
</ul>
<center>
<input type="submit" name="submit" value="Search">
</center>
</form>
<?php
} else {
    // form submitted, search
}
// clean up
mysql_close($connection);
?>

</body>
</html>

Here's what the form looks like.

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.

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.

<?php

// search.php - search for specific applications

// includes

// open connection to database
$connection = mysql_connect($hostname, $user, $pass) or die("Unable to connect!");

if (!$submit) {
    // form not yet submitted, display form
} else {
    // form submitted, search

    // check for missing parameters
    if (!$jcode || $jcode == "") {
        header("Location:error.php");
        exit;
    }

    // set up basic query and joins
    $query = "SELECT DISTINCT r_user.rid, r_user.lname, r_user.fname, r_user.email from r_user, r_skill, r_education WHERE r_user.jcode = '$jcode'";

    // if skills criteria selected
    if (!empty($skills) && !empty($exp_modifier) && !empty($years)) {
        // modify query further
        $query .= " AND r_user.rid = r_skill.rid  AND (";
        // tokenize keyword list
        $keywords = split(" ", $skills);
        // iterate through list
        for ($x=0; $x<sizeof($keywords); $x++) {
            trim($keywords[$x]);
            // this searches for skill1 AND skill2 AND ...
            // make this OR if you want an OR-type search
            if ($x != 0) {
                $query .= " AND";
            }
            $query .= " (r_skill.skill LIKE '%" . $keywords[$x] ."%' AND r_skill.experience " .  $exp_modifier . $years . " )";
        }
        $query .= ")";
    }

    // if education criterial selected
    if (!empty($degree) && !empty($subject)) {
        // modify query further
        $query .= " AND r_user.rid = r_education.rid AND r_education.fk_degree = '$degree' AND r_education.fk_subject = '$subject'";
    }

    // execute query
    $result = mysql_db_query($database, $query, $connection) or die("Error in query: $query. " . mysql_error());
    // number of records found
    $count = mysql_num_rows($result);

    // uncomment for debug purposes - echo the query
// echo $query . "<p>"; ?>
<html>
<head>
<basefont face="Verdana" size="2">
</head>

<body bgcolor=white>

<?php $image="search.jpg"; ?>
<?php include("header.inc.php"); ?>

Your search returned <?php echo $count; ?> match(es)
<p>
<ul>
<?php
// list matches
while (list($rid, $lname, $fname, $email) = mysql_fetch_row($result)) {
    echo "<li><a href=resume_details.php?rid=$rid>$lname, $fname &lt;$email&gt;</a>";
} ?>
</ul>
<?php
}
// clean up
mysql_close($connection);
?>

<?php include("footer.inc.php"); ?>

</body>
</html>

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.

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.

<?php
// resume_details.php - build a resume

// includes
include("config.php");
include("functions.php");

// check for missing parameters
if (!$rid || $rid == "") {
    header("Location:error.php");
    exit;
}

// open connection to database
$connection = mysql_connect($hostname, $user, $pass) or die("Unable to connect!");

// get personal information
$query = "SELECT fname, lname, dob, addr1, addr2, city, state, zip, country, phone, email, url, relo, posted from r_user, country WHERE r_user.fk_country = country.id AND rid = '$rid'";
$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($fname, $lname, $dob, $addr1, $addr2, $city, $state, $zip, $country, $phone, $email, $url, $relo, $posted) = mysql_fetch_row($result); ?>

<html>
<head>
<basefont face="Verdana" size="2">
</head>

<body bgcolor=white>

<?php $image="resume.jpg"; ?>
<?php include("header.inc.php"); ?>

<img src="images/pi.gif">
<br>
<b>Name:</b> <?php echo $fname . " " . $lname; ?>
<p>
<b>Date of birth:</b> <?php echo fixDate($dob); ?>
<p>
<b>Address:</b><br> <?php echo "$addr1<br>";
    if ($addr2) {
        echo "$addr2<br>";
    }
    echo "$city $zip<br>$state, $country"; ?>
<p>
<b>Phone:</b> <?php echo $phone; ?>
<p>
<b>Email address: </b><a href="mailto:<?php echo $email; ?>"><?php echo $email; ?></a>
<p>
<b>Web site:</b> <?php if ($url) {
        echo "<a target=new href=$url>$url</a>";
    } else {
        echo "None";
    } ?>
<p>

<?php
// get education history
$query = "SELECT institute, degree, subject, year from r_education, degree, subject WHERE r_education.fk_degree = degree.id AND r_education.fk_subject = subject.id AND rid = '$rid' ORDER BY year";
    $result = mysql_db_query($database, $query, $connection) or die("Error in query: $query. " . mysql_error());

    if (mysql_num_rows($result) > 0) {
        echo "<img src=images/ed.gif><br>";

        while (list($institute, $degree, $subject, $year) = mysql_fetch_row($result)) {
            echo "<b>Institute:</b> $institute<br>";
            echo "<b>Degree:</b> $degree ($subject, $year)<p>";
        }
    } ?>

<?php
// get employment history
$query = "SELECT employer, industry, start_year, end_year, responsibilities from r_employment, industry WHERE r_employment.fk_industry = industry.id AND rid = '$rid' ORDER BY end_year";
    $result = mysql_db_query($database, $query, $connection) or die("Error in query: $query. " . mysql_error());

    if (mysql_num_rows($result) > 0) {
        echo "<img src=images/em.gif><br>";
        while (list($employer, $industry, $start_year, $end_year, $responsibilities) = mysql_fetch_row($result)) {
            echo "<b>Employer</b>: $employer ($start_year-$end_year)<br>";
            echo "<b>Industry</b>: $industry<br>";
            echo "<b>Responsibilities</b>: <br>$responsibilities<p>";
        }
    } ?>

<?php
// get skills
$query = "SELECT skill, experience from r_skill WHERE rid = '$rid' ORDER BY experience";
    $result = mysql_db_query($database, $query, $connection) or die("Error in query: $query. " . mysql_error());

    if (mysql_num_rows($result) > 0) {
        echo "<img src=images/sk.gif><br>";

        while (list($skill, $experience) = mysql_fetch_row($result)) {
            echo "<b>$skill</b><br>";
            echo "$experience years experience<p>";
        }
    } ?>

<?php
// get references
$query = "SELECT name, phone, email from r_reference WHERE rid = '$rid'";
    $result = mysql_db_query($database, $query, $connection) or die("Error in query: $query. " . mysql_error());

    if (mysql_num_rows($result) > 0) {
        echo "<img src=images/ref.gif><br>";
        while (list($name, $phone, $ref_email) = mysql_fetch_row($result)) {
            echo "<b>Name:</b> $name<br>";
            echo "<b>Phone:</b> $phone<br>";
            if ($ref_email) {
                echo "<b>Email address:</b> <a href=mailto:$ref_email>$ref_email</a><p>";
            } else {
                echo "<p>";
            }
        }
    }
    mysql_close($connection); ?>

<img src="images/misc.gif">
<br>
<b>Willing to relocate:</b> <?php if ($relo == 1) {
        echo "Yes";
    } else {
        echo "No";
    } ?>
<p>
Resume posted on <b><?php echo fixDate($posted); ?></b>
<p>
<a href="javascript:history.back()">Go back to applicant list</a>, or <a href="search.php">search again</a>

<?php include("footer.inc.php"); ?>

</body>
</html>
<?php
}
?>

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.

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.

<html>
<head>
<basefont face="Verdana" size="2">
</head>

<body bgcolor=white>

<?php $image="error.jpg"; ?>
<?php include("header.inc.php"); ?>

There was an error accessing the page you requested. Please <a href="job_list.php">return to the main page</a> and try again.

<?php include("footer.inc.php"); ?>

</body>
</html>

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:

The Fundamentals of Relational Database Design: http://www.microsoft.com/TechNet/Access/technote/ac101.asp?a=printable

Protecting Web pages with HTTP authentication: http://www.apacheweek.com/issues/96-10-18#userauth

mySQL functions available in PHP: http://www.php.net/manual/en/ref.mysql.php

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.

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 on09 Jul 2001.