The Perfect Job - Part 2

Unfinished Business

In the first part of this article, I explained some of the problems typically associated with data management in a HR department, and put together a functional specification for a Web-based application to make the task easier. After putting together a basic database schema and normalizing it, I proceeded to develop scripts to display job listings, accept user applications, and store these applications in the database.

While the first part of this article described the user experience, I have not yet addressed the issues of updating the job board with new information, removing existing entries, or searching for potential candidates - all of which formed part of the initial feature set of this application. And so, in this concluding article, I'm going to wrap things up by looking at some of the tasks an administrator would need to accomplish in such a system, and developing some simple scripts to accomplish these.

Administrator Ahoy!

If you remember, when I first put together the "listings" table, I added a couple of dummy entries to it, just to get things rolling.

Now, that's fine during the early stages of application development... but you can't expect your customers to pop open a mySQL prompt every time they need to update a job listing. For one thing, they may not have the technical acumen necessary to manipulate a mySQL database; for another, they may prefer a pretty GUI to an ugly command-line. Either way, as the developer, you're on the hook to package those functions into your application as well.

Consequently, I'll begin by putting together an entry point for administrators, in much the same way as I did for users. This page will serve as a menu to the different administration functions available.

<?  
// admin.php - admin functions entry point
// includes

// 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());

// generate a table
echo "<table border=0 cellspacing=5 cellpadding=5>";

// iterate through resultset
while(list($id, $department) = mysql_fetch_row($result))
{

// print department name
echo "<tr><td colspan=4><b>Department:</b> $department</td></tr>";

// look for jobs within the department and print as list,  
add edit and delete links
$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());
while(list($jcode, $dsg) = mysql_fetch_row($result2))
{
echo "<tr><td width=10>&nbsp;</td><td>$dsg ($jcode)</td>  
<td><a href=edit.php?jcode=$jcode>
<font size=-1>edit</font></a></td> <td><a href=delete.php?jcode=$jcode>
<font size=-1>delete</font>
</a></td></tr>";
}
}
echo "</table>";
?>
<!-- snip -->

<a href="add.php">Add a new listing</a> or <a href="search.php">search  
the database</a>

<!-- snip -->

Here's what it looks like.

As you can see, it's almost identical to the user entry point, with the exception of the edit and delete links next to each job. Each of these calls a script to perform the appropriate function. The bottom of the page also includes links to add a new listing, or search the database for potential candidates.

It should be noted at this point that access to all these administration scripts should be restricted to privileged users only. The easiest way to do this is to move them into a separate directory, and protect it using Web-based authentication.

Copyright Melonfire, 2000. All rights reserved.