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

Building The Foundation - 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


With the database designed and out of the way, it's time to start writing some code to interface with it. The first script, "job_list.php", is the entry point to the application, and simply displays a list of available jobs, classified by department.

<? // job_list.php - display list of open jobs // includes include("config.php"); include("functions.php"); ?> <html> <head> <basefont face="Verdana" size="2"> </head> <body bgcolor=white> <? $image="listings.jpg"; ?> <? include("header.inc.php"); ?> <? // generate list ?> <? include("footer.inc.php"); ?> </body> </html>
Each page generated through this application has a particular layout - a blue banner at the top, a logo at the right, and a title (actually an image). The bottom of every page has a copyright notice and a disclaimer. Since these elements will remain constant, through the application, I've placed the corresponding HTML code in separate header and footer files, and simply include()d them on each page.

Again, by separating common interface elements into separate files, I've made it easier to customize the look of the application; simply alter these files, and the changes will be reflected on all the pages.

The variable $image stores the name of the image title for each page, and is used by "header.inc.php" - as you can see.

<!-- appears at the top of every page --> <table bgcolor="6583C3" width="100%" cellspacing="0" cellpadding="0"> <tr> <td height=50 align=right> <img src="images/header.gif" alt="" border="0"></td> </tr> </table> <p> <img src="images/<? echo $image; ?>" alt="" border="0"> <p> <!-- end of header.inc -->
In addition, two other files, "config.php" and "functions.php" are also included at the top of every page - they store database access information and useful functions, respectively.

<? // config.php - useful variables // database parameters // alter this as per your configuration $database="jobs"; $user = "root"; $pass = ""; $hostname = "localhost"; ?>
Back to "job_list.php" - here's the code that takes care of connecting to the database and actually generating the job listing.

<? include("header.inc.php"); ?> <? // open connection to database $connection = mysql_connect($hostname, $user, $pass) or die ("Unable to connect!"); // get list of departments with open jobs $query = "SELECT DISTINCT id, department from department, listing WHERE department.id = listing.fk_department"; $result = mysql_db_query($database, $query, $connection) or die ("Error in query: $query. " . mysql_error()); // iterate through resultset while(list($id, $department) = mysql_fetch_row($result)) { // print department name echo "<b>Department:</b> $department"; // look for jobs within the department and print as list $query2 = "SELECT jcode, designation from listing WHERE listing.fk_department = '$id'"; $result2 = mysql_db_query($database, $query2, $connection) or die ("Error in query: $query2. " . mysql_error()); echo "<ul>"; while(list($jcode, $dsg) = mysql_fetch_row($result2)) { echo "<li><a href=job_details.php?jcode=$jcode>$dsg ($jcode)</a>"; } echo "</ul>"; echo "<p>"; } // clean up mysql_close($connection); ?> <? include("footer.inc.php"); ?>
In this case, I've first queried the "listing" table for a list of departments holding open jobs - note the DISTINCT keyword to eliminate duplicate entries. Then, for each of those departments, I've printed the job designation and job code, and linked it to a script which will display further information.

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: