The Devil Is In The Details - 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.

TABLE OF CONTENTS:

By: icarus, (c) Melonfire

Poor Best

Rating:

/ 4

June 28, 2001

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE


<?
// 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> <? echo $designation; ?>
<p>
<b>Department:</b> <? echo $department; ?>
<p>
<!-- snip -->
<b>Posted on:</b> <? echo fixDate($posted); ?>
<p>
<!-- link to application form -->
<a href="apply.php?jcode=<? echo $jcode; ?>">Apply online</a> for this job,
or <a href="job_list.php">return to job listings</a>
<!-- snip -->
<?
}
?>

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".


<?
// 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: