Home arrow MySQL arrow Page 9 - The Perfect Job (part 1)

Applying Yourself - 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.

  1. The Perfect Job (part 1)
  2. An Ideal World
  3. Entry Point
  4. Going To The Database
  5. The Five Rs
  6. Lucky Thirteen
  7. Building The Foundation
  8. The Devil Is In The Details
  9. Applying Yourself
  10. Testing Times
  11. Filing It All Away
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 4
June 28, 2001

print this article


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.

>>> More MySQL Articles          >>> More By icarus, (c) Melonfire

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: