Calculating hours worked

PHP
1

Hi, I’m setting up a timesheet system with PHP / MySQL and can’t figure out how I can calculate hours worked based on a start time, end time and deducting a lunch time value. I saw another thread for this but no information about how the functions could be used.

Example:

Someone starts at 08:30 and finishes at 17:00, with a half-hour lunch. I have three fields for this- StartTime, LunchTime (the user enters 00:30 for half an hour) and EndTime. What I need is for the system to look at these times and work out that 8 hours have been worked, and enter this as an integer when the form has been sent (it doesn’t need to go into the database, just on the summary / confirmation page for the person who’s filling in the timesheets)

Likewise, If they started a quarter of an hour later, it should return 7.75.

That way, I can easily add up the total number of hours worked for the week, multiply by the pay rate and therefore report on the pay.

Anyone know how this can be done? Thanks.

2

Can you post the code you’ve got so far?

3

Sure:

This is the timesheet form that the user fills in


<?php
include ('inc/dbconnect.php');

$searchstring = mysql_real_escape_string($_GET['ID']);

$query = "select * from contractors WHERE `ID` = '$searchstring'"; // specify the table and field names for the SQL query

 $numresults=mysql_query($query);
 $numrows=mysql_num_rows($numresults);

if ($numrows == 0)
  {
  echo "<p>No record matching these details currently exists. Please check that the details are correct.</p>";
  }
// get results
  $result = mysql_query($query) or die("The Admin system is undergoing maintenance at the moment and will be available shortly");

// begin to show results set
$count = 1 + $s ;
// display the results returned
while ($row= mysql_fetch_array($result)) {
  $id = $row["ID"];
  $surname = $row["Surname"];
  $firstname = $row["Firstname"];
  $dateadded = $row["DateAdded"];
  $datelastmodified = $row["DateLastModified"];
  		//Date formatting for output in the summary screen
		$dateaddednice = date("F j, Y", strtotime($dateadded));
		$datelastmodifiednice = date("F j, Y", strtotime($datelastmodified));
  
  echo '<br /><h3>Creating timesheet for ' .$firstname. ' ' .$surname. '</h3>';
	echo '<form action="createtimesheet-addtotable.php" method="post" name="createtimesheet-addtotable">
	          <table width="1000" border="0" cellspacing="5" cellpadding="5" >
		      <tr>
			  <td align="left">Surname</td>
			  <td align="left"><input type="hidden" name="_AddedBy" value="[[+username]]" /><input name="_ID" value= ' . $id . ' readonly type="hidden" /><input name="_Surname" class="textinput" readonly size="50" value="'.$surname.'" /></td>
			  </tr>
			  <tr>
			  <td align="left">First Name</td>
			  <td align="left"><input name="_Firstname" class="textinput" readonly size="50" value="'.$firstname.'" /></td>
			  </tr>
			  <tr>
			  <td align="left">Client</td>
			  <td align="left"><select name="_Client" class="textinput" size="1">';
			  			$sql = "SELECT * FROM clients ORDER BY ClientName ASC";
						$rs = mysql_query($sql);                             
							while($row = mysql_fetch_array($rs)){                 
        						echo "<option value = '".$row['ClientName']."'>".$row['ClientName']."</option>";   
								}
			  echo '</select></td>
			  </tr>
			  <tr>
			  <td align="left">Week commencing<br /><span class="formnotes">Important: use the Select Date link to ensure that the date is in the right format for the database</span></td>
			  <td align="left">';
			  echo "<input name=\\"_WeekCommencing\\" class=\\"smallinput\\" size=\\"50\\" />  <a href=\\"javascript:showCal('Calendar1')\\">Select Date</a></td>";
			  echo '</tr>
			  <tr>
			  </table><br /><br />';
//Timesheet table
echo '<span class="formnotes">Enter Rate per hour, without pound symbol. For example, £25 per hour should be written as 25.00. Enter start and end times as 24-hour clock times, for example 08:00 and 17:00.</span><br /><br />';
	echo '<table width="800" border="0" cellspacing="5" cellpadding="5">
	      <tr>
		  <td width="120">Monday</td><td width="200">Rate <input name="_MondayRate" class="smallinput" size="10" /></td><td width="200">Start time <input name="_MondayStartTime" class="smallinput" size="10" /><td width="200">End time <input name="_MondayEndTime" class="smallinput" size="10" /><td width="200">Lunch <input name="_MondayLunch" class="smallinput" size="10" /></td>
		  </tr>
	      <tr>
		  <td width="120">Tuesday</td><td width="200">Rate <input name="_TuesdayRate" class="smallinput" size="10" /></td><td width="200">Start time <input name="_TuesdayStartTime" class="smallinput" size="10" /><td width="200">End time <input name="_TuesdayEndTime" class="smallinput" size="10" /></td><td width="200">Lunch <input name="_TuesdayLunch" class="smallinput" size="10" /></td>
		  </tr>
	      <tr>
		  <td width="120">Wednesday</td><td width="200">Rate <input name="_WednesdayRate" class="smallinput" size="10" /></td><td width="200">Start time <input name="_WednesdayStartTime" class="smallinput" size="10" /><td width="200">End time <input name="_WednesdayEndTime" class="smallinput" size="10" /></td><td width="200">Lunch <input name="_WednesdayLunch" class="smallinput" size="10" /></td>
		  </tr>
	      <tr>
		  <td width="120">Thursday</td><td width="200">Rate <input name="_ThursdayRate" class="smallinput" size="10" /></td><td width="200">Start time <input name="_ThursdayStartTime" class="smallinput" size="10" /><td width="200">End time <input name="_ThursdayEndTime" class="smallinput" size="10" /></td><td width="200">Lunch <input name="_ThursdayLunch" class="smallinput" size="10" /></td>
		  </tr>
	      <tr>
		  <td width="120">Friday</td><td width="200">Rate <input name="_FridayRate" class="smallinput" size="10" /></td><td width="200">Start time <input name="_FridayStartTime" class="smallinput" size="10" /><td width="200">End time <input name="_FridayEndTime" class="smallinput" size="10" /></td><td width="200">Lunch <input name="_FridayLunch" class="smallinput" size="10" /></td>
		  </tr>
	      <tr>
		  <td width="120">Saturday</td><td width="200">Rate <input name="_SaturdayRate" class="smallinput" size="10" /></td><td width="200">Start time <input name="_SaturdayStartTime" class="smallinput" size="10" /><td width="200">End time <input name="_SaturdayEndTime" class="smallinput" size="10" /></td><td width="200">Lunch <input name="_SaturdayLunch" class="smallinput" size="10" /></td>
		  </tr>
	      <tr>
		  <td width="120">Sunday</td><td width="200">Rate <input name="_SundayRate" class="smallinput" size="10" /></td><td width="200">Start time <input name="_SundayStartTime" class="smallinput" size="10" /><td width="200">End time <input name="_SundayEndTime" class="smallinput" size="10" /></td><td width="200">Lunch <input name="_SundayLunch" class="smallinput" size="10" /></td>
		  </tr>
		  </table><br />';

		echo '<input type="submit" class="signinbutton" value="Create Timesheet" name="createtimesheet-addtotable" /></form>';

  $count++ ;
  }
  ?>

And this is the form handler code (I haven’t added in anything to convert or calculate times worked etc. yet so it’s just a simple insert to the db)


<?php
include ('inc/dbconnect.php');

if($_POST['createtimesheet-addtotable'])
{
  $contractorid = mysql_real_escape_string(@$_POST['_ID']);
  $surname = mysql_real_escape_string(@$_POST['_Surname']);
  $firstname = mysql_real_escape_string(@$_POST['_Firstname']);
  $clientname = mysql_real_escape_string(@$_POST['_ClientName']);
  $weekcommencing = mysql_real_escape_string(@$_POST['_WeekCommencing']);
  
  $mondayrate = mysql_real_escape_string(@$_POST['_MondayRate']);
  $mondaystarttime = mysql_real_escape_string(@$_POST['_MondayStartTime']);
  $mondayendtime = mysql_real_escape_string(@$_POST['_MondayEndTime']);
  
  $tuesndayrate = mysql_real_escape_string(@$_POST['_TuesdayRate']);
  $tuesdaystarttime = mysql_real_escape_string(@$_POST['_TuesdayStartTime']);
  $tuesdayendtime = mysql_real_escape_string(@$_POST['_TuesdayEndTime']);
  
  $wednesdayrate = mysql_real_escape_string(@$_POST['_WednesdayRate']);
  $wednesdaystarttime = mysql_real_escape_string(@$_POST['_WednesdayStartTime']);
  $wednesdayendtime = mysql_real_escape_string(@$_POST['_WednesdayEndTime']);
  
  $thursdayrate = mysql_real_escape_string(@$_POST['_ThursdayRate']);
  $thursdaystarttime = mysql_real_escape_string(@$_POST['_ThursdayStartTime']);
  $thursdayendtime = mysql_real_escape_string(@$_POST['_ThursdayEndTime']);
  
  $fridayrate = mysql_real_escape_string(@$_POST['_FridayRate']);
  $fridaystarttime = mysql_real_escape_string(@$_POST['_FridayStartTime']);
  $fridayendtime = mysql_real_escape_string(@$_POST['_FridayEndTime']);
  
  $saturdayrate = mysql_real_escape_string(@$_POST['_SaturdayRate']);
  $saturdaystarttime = mysql_real_escape_string(@$_POST['_SaturdayStartTime']);
  $saturdayendtime = mysql_real_escape_string(@$_POST['_SaturdayEndTime']);
  
  $sundayrate = mysql_real_escape_string(@$_POST['_SundayRate']);
  $sundaystarttime = mysql_real_escape_string(@$_POST['_SundayStartTime']);
  $sundayendtime = mysql_real_escape_string(@$_POST['_SundayEndTime']);
  
  $dateadded = date("Y-m-d");
  $addedby = mysql_real_escape_string(@$_POST['_AddedBy']);

// Build SQL Query  
$query = "INSERT INTO timesheets (ContractorID, Surname, Firstname, ClientName, WeekCommencing, MondayRate, MondayStartTime, MondayEndTime, MondayLunch, TuesdayRate, TuesdayStartTime, TuesdayEndTime, TuesdayLunch, WednesdayRate, WednesdayStartTime, WednesdayEndTime, WednesdayLunch, ThursdayRate, ThursdayStartTime, ThursdayEndTime, ThursdayLunch, FridayRate, FridayStartTime, FridayEndTime, FridayLunch, SaturdayRate, SaturdayStartTime, SaturdayEndTime, SaturdayLunch, SundayRate, SundayStartTime, SundayEndTime, SundayLunch, DateAdded, AddedBy) VALUES ('$contractorid','$surname','$firstname','$clientname','$weekcommencing','$mondayrate','$mondaystarttime','$mondayendtime','$mondaylunch','$tuesdayrate','$tuesdaystarttime','$tuesdayendtime','$tuesdaylunch','$wednesdayrate','$wednesdaystarttime','$wednesdayendtime','$wednesdaylunch','$thursdayrate','$thursdaystarttime','$thursdayendtime','$thursdaylunch','$fridayrate','$fridaystarttime''$fridayendtime','$fridaylunch','$saturdayrate','$saturdaystarttime','$saturdayendtime','$saturdaylunch','$sundayrate','$sundaystarttime','$sundayendtime','$sundaylunch','$dateadded','$addedby')"; // specify the table and field names for the SQL query
if($result = mysql_query($query)) 
{ 	
    header('location: admin-confirmed.html');
} else {
     echo "ERROR: File not moved correctly";
}
}

?>
4

Sorry, this is the correct form handler code:


<?php
include ('inc/dbconnect.php');

if($_POST['createtimesheet-addtotable'])
{
  $contractorid = mysql_real_escape_string(@$_POST['_ID']);
  $surname = mysql_real_escape_string(@$_POST['_Surname']);
  $firstname = mysql_real_escape_string(@$_POST['_Firstname']);
  $clientname = mysql_real_escape_string(@$_POST['_ClientName']);
  $weekcommencing = mysql_real_escape_string(@$_POST['_WeekCommencing']);
  
  $mondayrate = mysql_real_escape_string(@$_POST['_MondayRate']);
  $mondaystarttime = mysql_real_escape_string(@$_POST['_MondayStartTime']);
  $mondayendtime = mysql_real_escape_string(@$_POST['_MondayEndTime']);
  $mondaylunchstart = mysql_real_escape_string(@$_POST['_MondayLunchStart']);
  $mondaylunchend = mysql_real_escape_string(@$_POST['_MondayLunchEnd']);
  
  $tuesdayrate = mysql_real_escape_string(@$_POST['_TuesdayRate']);
  $tuesdaystarttime = mysql_real_escape_string(@$_POST['_TuesdayStartTime']);
  $tuesdayendtime = mysql_real_escape_string(@$_POST['_TuesdayEndTime']);
  $tuesdaylunchstart = mysql_real_escape_string(@$_POST['_TuesdayLunchStart']);
  $tuesdaylunchend = mysql_real_escape_string(@$_POST['_TuesdayLunchEnd']);
  
  $wednesdayrate = mysql_real_escape_string(@$_POST['_WednesdayRate']);
  $wednesdaystarttime = mysql_real_escape_string(@$_POST['_WednesdayStartTime']);
  $wednesdayendtime = mysql_real_escape_string(@$_POST['_WednesdayEndTime']);
  $wednesdaylunchstart = mysql_real_escape_string(@$_POST['_WednesdayLunchStart']);
  $wednesdaylunchend = mysql_real_escape_string(@$_POST['_WednesdayLunchEnd']);
  
  $thursdayrate = mysql_real_escape_string(@$_POST['_ThursdayRate']);
  $thursdaystarttime = mysql_real_escape_string(@$_POST['_ThursdayStartTime']);
  $thursdayendtime = mysql_real_escape_string(@$_POST['_ThursdayEndTime']);
  $thursdaylunchstart = mysql_real_escape_string(@$_POST['_ThursdayLunchStart']);
  $thursdaylunchend = mysql_real_escape_string(@$_POST['_ThursdayLunchEnd']);
  
  $fridayrate = mysql_real_escape_string(@$_POST['_FridayRate']);
  $fridaystarttime = mysql_real_escape_string(@$_POST['_FridayStartTime']);
  $fridayendtime = mysql_real_escape_string(@$_POST['_FridayEndTime']);
  $fridaylunchstart = mysql_real_escape_string(@$_POST['_FridayLunchStart']);
  $fridaylunchend = mysql_real_escape_string(@$_POST['_FridayLunchEnd']);
  
  $saturdayrate = mysql_real_escape_string(@$_POST['_SaturdayRate']);
  $saturdaystarttime = mysql_real_escape_string(@$_POST['_SaturdayStartTime']);
  $saturdayendtime = mysql_real_escape_string(@$_POST['_SaturdayEndTime']);
  $saturdaylunchstart = mysql_real_escape_string(@$_POST['_SaturdayLunchStart']);
  $saturdaylunchend = mysql_real_escape_string(@$_POST['_SaturdayLunchEnd']);
  
  $sundayrate = mysql_real_escape_string(@$_POST['_SundayRate']);
  $sundaystarttime = mysql_real_escape_string(@$_POST['_SundayStartTime']);
  $sundayendtime = mysql_real_escape_string(@$_POST['_SundayEndTime']);
  $sundaylunchstart = mysql_real_escape_string(@$_POST['_SundayLunchStart']);
  $sundaylunchend = mysql_real_escape_string(@$_POST['_SundayLunchEnd']);
  
  $dateadded = date("Y-m-d");
  $addedby = mysql_real_escape_string(@$_POST['_AddedBy']);

// Build SQL Query  
$query = "INSERT INTO timesheets (ContractorID, Surname, Firstname, ClientName, WeekCommencing, MondayRate, MondayStartTime, MondayEndTime, MondayLunchStart, MondayLunchEnd, TuesdayRate, TuesdayStartTime, TuesdayEndTime, TuesdayLunchStart, TuesdayLunchEnd, WednesdayRate, WednesdayStartTime, WednesdayEndTime, WednesdayLunchStart, WednesdayLunchEnd, ThursdayRate, ThursdayStartTime, ThursdayEndTime, ThursdayLunchStart, ThursdayLunchEnd, FridayRate, FridayStartTime, FridayEndTime, FridayLunchStart, FridayLunchEnd, SaturdayRate, SaturdayStartTime, SaturdayEndTime, SaturdayLunchStart, SaturdayLunchEnd, SundayRate, SundayStartTime, SundayEndTime, SundayLunchStart, SundayLunchEnd, DateAdded, AddedBy) VALUES ('$contractorid','$surname','$firstname','$clientname','$weekcommencing','$mondayrate','$mondaystarttime','$mondayendtime','$mondaylunchstart','$mondaylunchend','$tuesdayrate','$tuesdaystarttime','$tuesdayendtime','$tuesdaylunchstart','$tuesdaylunchend','$wednesdayrate','$wednesdaystarttime','$wednesdayendtime','$wednesdaylunchstart','$wednesdaylunchend','$thursdayrate','$thursdaystarttime','$thursdayendtime','$thursdaylunchstart','$thursdaylunchend','$fridayrate','$fridaystarttime''$fridayendtime','$fridaylunchstart','$fridaylunchend','$saturdayrate','$saturdaystarttime','$saturdayendtime','$saturdaylunchstart','$saturdaylunchend','$sundayrate','$sundaystarttime','$sundayendtime','$sundaylunchstart','$sundaylunchend','$dateadded','$addedby')"; // specify the table and field names for the SQL query

echo $query;

if($result = mysql_query($query)) 
{ 	
$gethours = 'SELECT SUBTIME(TIMEDIFF(MondayEndTime, MondayStartTime), MondayLunch) FROM timesheets';

$result2 = mysql_query($gethours);
echo $result2;

    header('location: admin-confirmed.html');
} else {
     echo "ERROR: File not moved correctly";
}
}

?>
5

Please be aware that the mysql_* extension is now deprecated as of the current version of PHP and will very likely be removed from the next 5.x version and will likely not be in PHP 6.x (when it eventually is released). You should migrate over to either the mysqli_* extension or to PDO. PDO is a better choice as it doesn’t tie you down so much to a particular database server software.

Once you have migrated you should use Prepared Statements to prevent SQL Injection attacks. Have a read of this article from the PHP manual, it shows how to use prepared statements with PDO and also explains the principle.

6

take a look at strtotime

7

Have I understood this correctly.

The payrate can be different for each day?