The Perfect Job (part 2) - Building Blocks
(Page 5 of 7 )
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.
<?
// 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>
<? $image="search.jpg"; ?>
<? include("header.inc.php"); ?>
Your search returned <? echo $count; ?> match(es)
<p>
<ul>
<?
// list matches
while (list($rid, $lname, $fname, $email) = mysql_fetch_row($result))
{
echo "<li><a href=resume_details.php?rid=$rid>$lname, $fname
<$email></a>";
}
?>
</ul>
<?
}
// clean up
mysql_close($connection);
?>
<? 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.
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.
<?
// 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>
<? $image="resume.jpg"; ?>
<? include("header.inc.php"); ?>
<img src="images/pi.gif">
<br>
<b>Name:</b> <? echo $fname . " " . $lname; ?>
<p>
<b>Date of birth:</b> <? echo fixDate($dob); ?>
<p>
<b>Address:</b><br> <? echo "$addr1<br>"; if($addr2) { echo "$addr2<br>"; }
echo "$city $zip<br>$state, $country"; ?>
<p>
<b>Phone:</b> <? echo $phone; ?>
<p>
<b>Email address: </b><a href="mailto:<? echo $email; ?>"><? echo $email;
?></a>
<p>
<b>Web site:</b> <? if($url) { echo "<a target=new href=$url>$url</a>"; }
else { echo "None"; } ?>
<p>
<?
// 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>";
}
}
?>
<?
// 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>";
}
}
?>
<?
// 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>";
}
}
?>
<?
// 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> <? if($relo == 1) { echo "Yes"; } else { echo
"No"; } ?>
<p>
Resume posted on <b><? echo fixDate($posted); ?></b>
<p>
<a href="javascript:history.back()">Go back to applicant list</a>, or <a
href="search.php">search again</a>
<? include("footer.inc.php"); ?>
</body>
</html>
<?
}
?>
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.Next: Handling The Gray Areas >>
More MySQL Articles
More By icarus, (c) Melonfire
/ 2 
