The Perfect Job (part 2) - Changing Things Around
(Page 4 of 7 )
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.Next: Building Blocks >>
More MySQL Articles
More By icarus, (c) Melonfire
/ 2 
