The Perfect Job (part 1) - Applying Yourself
(Page 9 of 11 )
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.
<?
// 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="<? 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">
<?
// 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">
<? for ($x=1; $x<=31; $x++) { echo "<option value=\"" . sprintf("%02d", $x)
. "\">" . sprintf("%02d", $x) . "</option>"; } ?>
</select> -
<select name="mm">
<? 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) -->
<? 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>
<?
// 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[]">
<?
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[]">
<?
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>
<?
}
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>
<?
// 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 [<? echo ($x+1); ?>]
<? if ($x == 0) { echo "<br><font size=-2>(example: ABC, Inc.)</font>"; } ?>
</td>
<td>Industry
<? if ($x == 0) { echo "<br><font size=-2>(example: Advertising)</font>"; }
?></td>
<td>Start year
<? if ($x == 0) { echo "<br><font size=-2>(example: 1996)</font>"; } ?>
</td>
<td>End year
<? 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[]">
<?
// 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
<? 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>
<?
}
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.
This article copyright Melonfire 2001. All rights reserved.Next: Testing Times >>
More MySQL Articles
More By icarus, (c) Melonfire
/ 4 
