In this concluding article, explore the scripts which add andremove timesheet entries to the system, and get a crash course instatistics by using these entries to generate useful resource allocationand usage reports.
Let's now look at the code which connects to the database and retrieves a list of entries, given the user and date.
<!-- table (1r, 3c) -->
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td valign="top" align="left" width="60%">
<!-- table for existing timesheet records goes here -->
<?
// create datestamp
$datestamp = $y . "-" . $m . "-" . $d;
// initialize variable to hold total hours worked
$totalHours = 0;
// open connection to database
$connection = mysql_connect($hostname, $user, $pass) or die ("Unable to
connect!");
// get timesheet records for this user and this date
$query = "SELECT lid, pname, tname, hours FROM projects, tasks, log WHERE
projects.pid = log.pid AND tasks.tid = log.tid AND date = '$datestamp' AND
uid = '$SESSION_UID'";
$result = mysql_db_query($database, $query, $connection) or die ("Error in
query: $query. " . mysql_error());
?>
<table border="0" cellspacing="2" cellpadding="2">
<tr>
<td colspan=4><b><font color="#3098C3">Timesheet for <? echo
fixDate($datestamp); ?></font></b></td>
</tr>
<?
// if records exist
if (mysql_num_rows($result) > 0)
{
?>
<form action="delete.php" method="post">
<tr>
<td><b>Project</b></td>
<td><b>Task</b></td>
<td><b>Hours</b></td>
<td> </td>
</tr>
<?
// display
while (list($lid, $pname, $tname, $hours) = mysql_fetch_row($result))
{
$totalHours= $totalHours + $hours;
?>
<tr>
<td><? echo $pname; ?></td>
<td><? echo $tname; ?></td>
<td align="center"><? echo sprintf("%1.01f", $hours); ?></td>
<td><input type="Checkbox" name="lid[]" value="<? echo $lid; ?>"></td>
</tr>
<?
}
?>
<tr>
<td colspan=2> </td>
<td align="center"><hr><b><? echo sprintf("%1.01f", $totalHours);
?></b></td>
<td> </td>
</tr>
<tr>
<td colspan="4" align="right"><input type="Submit" name="submit"
value="Delete Selected Records"></td>
</tr>
<!-- send date as hidden values - used to redirect back to this page -->
<input type="hidden" name="d" value="<? echo $d; ?>">
<input type="hidden" name="m" value="<? echo $m; ?>">
<input type="hidden" name="y" value="<? echo $y; ?>">
</form>
<?
}
// if no existing records
// display message
else
{
?>
<tr>
<td colspan=4>No records found</td>
</tr>
<?
}
?>
</table>
</td>
<!-- spacer -->
<td width="10%">
</td>
<td valign="top" align="left" width="30%">
<!-- table for new timesheet records - snip -->
</td>
</tr>
</table>
Here are a few quick notes on how this code works:
1.
The first order of business is to open a connection to the database and retrieve a list of entries for this user and this date; I've used a join so that I can display descriptive project and task names rather than meaningless IDs.
<?
// get timesheet records for this user and this date
$query = "SELECT lid, pname, tname, hours FROM projects, tasks, log WHERE
projects.pid = log.pid AND tasks.tid = log.tid AND date = '$datestamp' AND
uid = '$SESSION_UID'";
$result = mysql_db_query($database, $query, $connection) or die ("Error in
query: $query. " . mysql_error());
?>
2. Before proceeding to use the generated resultset, I'm also
initializing a variable named $totalHours - this variable will be used to calculate the total for the hours column in the list displayed.
<?
// initialize variable to hold total hours worked
$totalHours = 0;
?>
3. The fixDate() function call in the first row of the table
<tr>
<td colspan=4><b><font color="#3098C3">Timesheet for <? echo
fixDate($datestamp); ?></font></b></td>
</tr>
is used to create a human-readable version of the $datestamp
variable. Here's what the function looks like:
<?
// function to format DATE values
function fixDate($val)
{
// split it up into components
$datearr = explode("-", $val);
// create a timestamp with mktime(), format it with date()
return date("d M Y", mktime(0, 0, 0, $datearr[1], $datearr[2],
$datearr[0]));
}
?>
4. With all that out of the way, I'm iterating through the
resultset to display each entry, complete with project name, task name and hours worked. Since I want to allow the user to delete selected entries, a form checkbox is added next to each entry as well.
Each iteration also adds the current hours value to the $totalHours value, which is displayed in a separate row once the resultset has been completely processed.
When the user hits the delete button and submits the form,
the selected entries will be passed to the form processor "delete.php" as an array - this array is named $lid (you can see it attached to each checkbox above) and contains the unique record identifier for each selected entry. The "delete.php" processor will use this information to identify which records are to be deleted from the "log" table.
5. Finally, I'm including the three date variables $d, $m and $y in the form as hidden values. My intent here is to pass these values to the "delete.php" script when the form is submitted; "delete.php", in turn, will use them to redirect the browser back to the correct "view.php" instance.
<!-- send date as hidden values - used to redirect back to this page -->
<input type="hidden" name="d" value="<? echo $d; ?>">
<input type="hidden" name="m" value="<? echo $m; ?>">
<input type="hidden" name="y" value="<? echo $y; ?>">
Here's what it looks like:
The right side of the page contains a form, which allows the user to add a new entry to the "log" table by specifying the hours worked on a specific task for a specific project.
<!-- table (1r, 3c) -->
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td valign="top" align="left" width="60%">
<!-- table for existing timesheet records goes here - snip -->
</td>
<!-- spacer -->
<td width="10%">
</td>
<td valign="top" align="left" width="30%">
<!-- table for new timesheet records goes here -->
<table border="0" cellspacing="2" cellpadding="2">
<form action="add.php" method="post">
<tr>
<td colspan="2"><b><font color="#3098C3">Add New Record</font></font></td>
</tr>
<tr>
<td colspan="2"><b>Project</b></td>
</tr>
<tr>
<td colspan="2">
<select name="pid">
<?
// get project list
$query = "SELECT pid, pname from projects";
$result = mysql_db_query($database, $query, $connection) or die ("Error in
query: $query. " . mysql_error());
while (list($pid, $pname) = mysql_fetch_row($result))
{
echo "<option value=$pid>$pname</option>";
}
mysql_free_result($result);
?>
</select>
</td>
</tr>
<tr>
<td><b>Task</b></td>
<td><b>Hours</b></td>
</tr>
<tr>
<td>
<select name="tid">
<?
// get task list
$query = "SELECT tid, tname from tasks";
$result = mysql_db_query($database, $query, $connection) or die ("Error in
query: $query. " . mysql_error());
while (list($tid, $tname) = mysql_fetch_row($result))
{
echo "<option value=$tid>$tname</option>";
}
mysql_free_result($result);
mysql_close($connection);
?>
</select>
</td>
<td>
<select name="h">
<?
for ($x=0.5; $x<=16; $x+=0.5)
{
echo "<option value=$x>" . sprintf("%1.01f", $x) . "</option>";
}
?>
</select>
</tr>
<tr>
<td colspan=2 align=right><input type="submit" name="submit" value="Add
New Record"></td>
</tr>
<!-- send date as hidden values - used to redirect back to this page -->
<input type="hidden" name="d" value="<? echo $d; ?>">
<input type="hidden" name="m" value="<? echo $m; ?>">
<input type="hidden" name="y" value="<? echo $y; ?>">
</form>
</table>
</td>
</tr>
</table>
This is much simpler to read and understand than the previous
listing. Essentially, I'm setting up drop-down boxes for the various project, task and hour values (in increments of 0.5), and inviting the user to add an entry to the timesheet by simply selecting appropriate values from each. I'm also including the three hidden date variables in the form, for the same reasons previously stated.
Here's what this half of the page looks like:
And here's what the complete product looks like:
In case you're still confused, don't give up just yet - the next couple of scripts should make things clearer.
/ 4 
