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

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.

  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 script "job_details.php" is designed to accept a particular job code, connect to the database, and print details such as qualifications and responsibilities for that job. It also includes a link to the job application form, should the user be interested in applying for the job.

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

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: