Home arrow MySQL arrow Page 4 - The Perfect Job (part 2)

Changing Things Around - MySQL

In the first part of this article, you built the architecturenecessary to accept and store resumes online. In this concluding part, findout how to make use of the stored data to find suitable candidates for aparticular job, and also read about the functions available to maintain andupdate the job listings.

  1. The Perfect Job (part 2)
  2. Administrator Ahoy!
  3. Adding To The Mix
  4. Changing Things Around
  5. Building Blocks
  6. Handling The Gray Areas
  7. Endgame
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 2
July 11, 2001

print this article


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.

<? // 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="<? echo $PHP_SELF; ?>" method="POST"> <input type=hidden name="jcode" value="<? echo $jcode; ?>"> <!-- job details --> <tr> <td>Designation<font color="red">*</font></td> <td width=30> </td> <td>Department<font color="red">*</font></td> </tr> <tr> <td><input type="text" name="dsg" size="25" value="<? echo $designation; ?>"></td> <td width=30> </td> <td><select name="dpt"> <? // 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> <? } else { // form submitted, process } ?>
Once the form has been submitted, an UPDATE query is executed to update the database with the new information.

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

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

This article copyright Melonfire 2001. All rights reserved.{mospagebreak title=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.

<? // 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> <? $image="search.jpg"; ?> <? include("header.inc.php"); ?> <form action="<? echo $PHP_SELF; ?>" method="post"> Display all applications for the post <select name="jcode"> <? // 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=""><unspecified></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=""><unspecified></option> <? // 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=""><unspecified></option> <? // 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> <? } 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.

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: