Home arrow PHP arrow Page 3 - Time Is Money (part 2)

Split Personality - PHP

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.

TABLE OF CONTENTS:
  1. Time Is Money (part 2)
  2. Getting Creative
  3. Split Personality
  4. In...
  5. ...And Out
  6. The Number Game
  7. Exercising Restraint
  8. The Big Picture...
  9. ...And The Little Brush Strokes
  10. When Things Go Wrong
  11. Happy Endings
By: The Disenchanted Developer, (c) Melonfire
Rating: starstarstarstarstar / 4
November 16, 2001

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
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>&nbsp;</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>&nbsp;</td> <td align="center"><hr><b><? echo sprintf("%1.01f", $totalHours); ?></b></td> <td>&nbsp;</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%"> &nbsp; </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.

<? // snip // 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> <? } ?>
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%"> &nbsp; </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.

 
 
>>> More PHP Articles          >>> More By The Disenchanted Developer, (c) Melonfire
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: