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.

TABLE OF CONTENTS:
  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
Poor Best
Rating: starstarstarstarstar / 4
June 28, 2001

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
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
   

MYSQL ARTICLES

- Xeround Releases Free Version of MySQL Cloud...
- Oracle Announces New MySQL Specialization
- Constant Contact Chooses SkySQL for MySQL Su...
- Revoke Statement in MySQL
- The Grant Statement in MySQL
- SuccessBricks Announces ClearDB Availability...
- Building a PHP ORM: Deploying a Blog
- TROSYS Launches Free MySQL Manager and Admin...
- Building an ORM in PHP: Domain Modeling
- Building an ORM in PHP
- MySQL Leads Open Source Market, Gets Cluster...
- Oracle Announces Milestone Release for MySQL
- How to Stop SQL Injection Attacks
- New Defragmentation Solution for SQL Server
- Comparison of MyISAM and InnoDB MySQL Databa...
Find More MySQL Articles


© 2003-2012 by Developer Shed. All rights reserved. DS Cluster 8 - Follow our Sitemap

Dev Shed Tutorial Topics: