Need to track volunteer hours

yancek · 10-01-2008, 09:31 AM

Quote:

I'd have the system read the current system date automatically. Stops the 'volunteers' fiddling the dates

They can't fiddle with the dates. I'm not explaining very well based on the last two posts. I used win32sux script from post #7 so that when a volunteer logs in by name, a unix timestamp is automatically posted in the mysql table. I wasn't aware that something like win32sux last suggestion was a possibility so I will do some research on that.

Quote:

automatically setting the time to midnight in the timestamp generated from your search form's input, and then telling MySQL to only show you records with timestamps between "that" and "that plus 86400"

I then added another row to the table for yearmonth. When volunteers login they are asked to enter the date. This is not used to determine hours but only for search purposes and is not an output in the scripts. It's a little cumbersome but, until I am able to implement win32sux suggestion, I guess this will work.

Thanks for your suggestion.

win32sux · 10-01-2008, 09:49 AM

Yeah, I'd recommend you start working to completely rid yourself of any non-timestamp storage in your database. That thing about having the guys enter dates on their own in order to use that for search later sounds really unnecessary, error-prone, and cumbersome IMHO. If you need help implementing the suggestion I gave you about "midnight" just ask.

yancek · 10-01-2008, 04:25 PM

I agree that having non-timestamp storage is obviously duplicative and a little cumbersome. I tried it on a test machine to see if it would output the data I wanted the way I wanted and it did.

I do need help implement your suggestion about a "midnight" entry. I understand the concept but am not sure how to implement it. I did a little googling earlier but haven't found anything satisfactory yet. Will keep trying and I would appreciate any help.

Thanks.

win32sux · 10-01-2008, 04:43 PM

Well, the way I picture it is like this example:

PHP Code:



$Day   = $_POST[day]; 
$Month = $_POST[month]; 
$Year  = $_POST[year]; 
 
$SearchDateStart = mktime(0,0,0,$Month,$Day,$Year); 
$SearchDateStop  = $SearchDateStart + 86400;

You just make sure the search form submits all three POST values as numericals.

Then the MySQL query could be like:

PHP Code:



$result = mysql_query("SELECT * FROM TimeSheet 
WHERE Date >= $SearchDateStart AND Date < $SearchDateStop");

Give it a shot and see if it works (I haven't tried it).

yancek · 10-02-2008, 04:26 PM

I've obviously got something wrong in the script. Database name 'timesheet', table 'hours', two fields 'name' and 'datetime'. Here's the php script I have:

PHP Code:



<?php 
mysql_connect("localhost","username","password"); 
mysql_select_db("timesheet"); 
$Day = $_POST['day']; 
$Month = $_POST['month']; 
$Year=$_POST['year']; 
$SearchDateStart = mktime(0,0,0,$month,$day,$year); 
$SearchDateStop = $SearchDateStart + 86400; 
$search-$_POST["search"]; 
$result = mysql_query("SELECT * FROM hours WHERE datetime >= $SearchDateStart AND datetime < $SearchDateStop"); 
echo "<table>"; 
while($row = mysql_fetch_array($result)) 
   { 
     $HumanDateTime = date("F j, Y (g:i A)",$row['datetime']); 
     echo "<tr><td>" . $row['name'] . "</td>"; 
     echo "<td>" . $HumanDateTime . "</td></tr>";   
   } 
echo "</table>"; 
?>

I understand how the part of the script you posted in your last post would work but don't see how the script could work. Something missing and I don't know what it is. I'm a little out of my depth here.

win32sux · 10-02-2008, 05:19 PM

Variable names are case-sensitive. The values you are feeding the mktime() function in your post above aren't the ones which were submitted via POST. Once you fix that you should verify that your form is submitting the POST values properly (you can test by echoing them on the search result page) before focusing on the query part.

BTW, what's this about:

PHP Code:



$search-$_POST["search"];

yancek · 10-02-2008, 06:10 PM

OK. Got it working. Thanks for your help. The entry below was part of a different search script I use when I was trying different things to get this to work, forgot to delete.

PHP Code:



$search-$_POST["search"];

win32sux · 10-02-2008, 06:24 PM

Quote:

Originally Posted by yancek

OK. Got it working. Thanks for your help.

You're welcome. Was the case sensitivity the problem or was it something else?

yancek · 10-03-2008, 04:29 PM

It was the case sensitivity in variable. Thanks again.