formatting time

Anon

Hello,

I am creating a timesheet for my department and having a hard time calculating the toal time of the day in my table. I tried using format coding but that is not working. Can you help me??

//This is my code for logging in the time and trying to calculate the total hours and minutes for the day.

$sql = "select * from st where rowid=$rowid";

$connection = mysql_connect("localhost","univrel","univrel") or die ("Couldn't connect to server.");

$db = mysql_select_db("univrel",$connection) or die ("Couldn't connect to database.");

$result = mysql_query($sql, $connection);

$row = mysql_fetch_object($result);

 	 $resultentry["date"] = $row->date;
 $resultentry["timeIn_one"] = substr($row->timeIn_one,0,strlen($row->timeIn_one)-3);
 	 $resultentry["timeOut_one"] = substr($row->timeOut_one,0,strlen($row->timeOut_one)-3);
 	 $resultentry["timeIn_two"] = substr($row->timeIn_two,0,strlen($row->timeIn_two)-3);
 $resultentry["timeOut_two"] = substr($row->timeOut_two,0,strlen($row->timeOut_two)-3);
 $resultentry["tot_hrs_day"] = substr($row->tot_hrs_day,0,strlen($row->tot_hrs_day)-3);

 	  printf("<input type=hidden name=rowid value='%s'>", $rowid);
 	  printf("Date:<br>
  	<input type=date name=date value='%s'><br><br>
  ", $resultentry["date"]);
  printf("Time In:<br>
  	<input type=time name=timeIn_one value='%s'><br><br>
  ", $resultentry["timeIn_one"]);
  printf("Time Out:<br>
  	<input type=time name=timeOut_one value='%s'><br><br>
  ", $resultentry["timeOut_one"]);
  printf("Time In:<br>
  	<input type=time name=timeIn_two value='%s'><br><br>
  ", $resultentry["timeIn_two"]);
  printf("Time Out:<br>
  	<input type=time name=timeOut_two value='%s'><br><br>
  ", $resultentry["timeOut_two"]);
  printf("Total Time of Day:<br>
  	<input type=time name=tot_hrs_day value='%s'><br><br>
  ", $resultentry["tot_hrs_day"]);
  printf("<input type=Submit value=Update time>");
  printf("</form");

mysql_free_result($result);

?>
<br>
<br><br>
<a href="dummyview.php3">Cancel</a>
</form>
</center>
</body>

I tried using VBscript at one time but that didnt work neither.

[deleted]

What format are the times in, in the database?

Are they strings in any special format or are they UNIX or MySQL timestamps?

-sridhar

Anon

The format in the Mysql database are '00:00:00' but in my php3 string i have a (strlen -3) eliminating the seconds(:00) so that the time stores just hours and minutes in the database...i dont want to use seconds.

What you think?

[deleted]

Hmm... I tried your code. It works on my PHP.

I also tried it on my dedicated server. It works there as well. On my dedicated machine, I have php3.0.7

Goto: http://216.97.124.59/dummyupdate.php

Of course it doesn't do anything when you click UpDate. But this is the format you wanted right????

Why didn't the code work under yours?

If you have a URL where I can see your output, let me know. I'll take a look.

-sridhar

Anon

Typing in the numbers is fine. My problem is getting the 'total time of day' field to automatically calculate as you input the time for the day in each field. Basically i would like the 'total time of day' field to calculate everytime a time is entered in each field.

I hope i'm not confusing you.

//Here is my output/view code
//URL: http://www.ur.msu.edu/workshop/artice/dummyview.php3
//It will contain some errors because of my formatting codes.

<h1>Student Timesheet</h1>

$sql = "SELECT * from st";

If ($date)
$sql .= "name like '%$date%' and ";
If ($timeIn_one)
$sql .= "email like '%$timeIn_one%' and ";
If ($timeOut_one)
$sql .= "password like '%$timeOut_one%' and ";
If ($timeIn_two)
$sql .= "department like '%$timeIn_two%' and ";
If ($timeOut_two)
$sql .= "department like '%$timeOut_two%' and ";
If ($tot_hrs_day)
$sql .= "department like '%$tot_hrs_day%' and ";

$connection = mysql_connect("localhost","univrel","univrel") or die ("Couldn't connect to server.");

$db = mysql_select_db("univrel",$connection) or die ("Couldn't connect to database.");

$sql_result = mysql_query($sql,$connection);

<?
while (($row = mysql_fetch_object($sql_result)))
{
printf("<tr>
<td><center>%s</center></td>
<td><center>%s</center></td>
<td><center>%s</center></td>
<td><center>%s</center></td>
<td><center>%s</center></td>
<td><center>format_time($tot_hrs_day);</center></td>
<td><a href=\"dummylogin.php3?rowid=%s\"><I>Log time</I></a></td>
</tr>\n", $row->date,
substr($row->timeIn_one,0,strlen($row->timeIn_one)-3),
substr($row->timeOut_one,0,strlen($row->timeOut_one)-3),
substr($row->timeIn_two,0,strlen($row->timeIn_two)-3),
substr($row->timeOut_two,0,strlen($row->timeOut_two)-3),
substr($row->tot_hrs_day,0,strlen($row->tot_hrs_day)-3),
$row->rowid);

}
printf("</table>\n");

mysql_free_result($sql_result);

</form>
</center>
</body>

//Here is my update code

$update = "Update st set date='$date', timeIn_one='${timeIn_one}:00', timeOut_one='${timeOut_one}:00', timeIn_two='${timeIn_two}:00', timeOut_two='${timeOut_two}:00', tot_hrs_day='${tot_hrs_day}:00' where rowid=$rowid";

$connection = mysql_connect("localhost","univrel","univrel") or die ("Couldn't connect to server.");

$db = mysql_select_db("univrel",$connection) or die ("Couldn't connect to database.");

$sql_result = mysql_query($update, $connection);

header ("location: dummyview.php3");
?>

Whats wrong with this??

Anon

Did you get my last message??

[deleted]

Artice,

Why don't you do this:

For each of the times (once you get it to the 00:00 format), use the explode function to split it into an array of hour and minute.

Then add them together:

$tarray = explode(":", $timeIn_one);
$tone_in = ($tarray[0]60) + $tarray[1];
$tarray = explode(":", $timeOut_one);
$tone_out = ($tarray[0]60) + $tarray[1];

Subtract $tone_in from $tone_out:

$total_timeOne = $tone_out - $tone_in;

So now $total_timeOne is in Minutes.
Do the same from timeTwo.
Add them together. Now you'll ask: How do I convert this to 00:00?

Easy...
assume $totaltime is the total time in minutes:

$hours = floor($totaltime/60);
$minutes = $totaltime%60;

$formatted_time = "$hours:$minutes";

I'm sure there are easier ways to do this.

hope this help,
-sridhar

P.S. If it doesn't work, let me know. I'll write up a code, test it, and post it up here.

Anon

Hi Sridhar,

I have been working with the code you gave me almost all night and still cant get it working! Am i putting code in the wrong places??? Where should i incorporate this code? Take a look at my codes...

//Output-view code

<body>
<center>
<h1>Student Timesheet</h1>

// For each of the times (once you get it to the 00:00 format), use the explode function to split it into an array of hour and minute.

// Then add them together:

$tarray = explode(":", $timeIn_one);
$tone_in = ($tarray[0]60) + $tarray[1];
$tarray = explode(":", $timeOut_one);
$tone_out = ($tarray[0]60) + $tarray[1];

// Subtract $tone_in from $tone_out:

$total_timeOne = $tone_out - $tone_in;

// So now $total_timeOne is in Minutes.
// Do the same from timeTwo.

$tarray = explode(":", $timeIn_two);
$ttwo_in = ($tarray[0]60) + $tarray[1];
$tarray = explode(":", $timeOut_two);
$ttwo_out = ($tarray[0]60) + $tarray[1];

// Subtract $ttwo_in from $ttwo_out:

$total_timeTwo = $ttwo_out - $ttwo_in;

// Add them together. Now you'll ask: How do I convert this to 00:00?

$totaltime = $total_timeOne + $total_timeTwo;

// Easy...
// assume $totaltime is the total time in minutes:

$hours = floor($totaltime/60);
$minutes = $totaltime%60;

$formatted_time = "$hours:$minutes";

$sql = "SELECT * from st";

$connection = mysql_connect("localhost","univrel","univrel") or die ("Couldn't connect to server.");

$db = mysql_select_db("univrel",$connection) or die ("Couldn't connect to database.");

$sql_result = mysql_query($sql,$connection);

<?
while (($row = mysql_fetch_object($sql_result)))
{
printf("<tr>
<td><center>%s</center></td>
<td><center>%s</center></td>
<td><center>%s</center></td>
<td><center>%s</center></td>
<td><center>%s</center></td>
<td><center>$formatted_time </center></td>
<td><a href=\"dummylogin.php3?rowid=%s\"><I>Log time</I></a></td>
</tr>\n", $row->date,
substr($row->timeIn_one,0,strlen($row->timeIn_one)-3),
substr($row->timeOut_one,0,strlen($row->timeOut_one)-3),
substr($row->timeIn_two,0,strlen($row->timeIn_two)-3),
substr($row->timeOut_two,0,strlen($row->timeOut_two)-3),
substr($row->formatted_time,0,strlen($row->formatted_time)-3),
$row->rowid);

}
printf("</table>\n");
mysql_free_result($sql_result);

?>
</form>
</center>
</body>

// Log time page

$sql = "select * from st where rowid=$rowid";

$connection = mysql_connect("localhost","univrel","univrel") or die ("Couldn't connect to server.");

$db = mysql_select_db("univrel",$connection) or die ("Couldn't connect to database.");

$result = mysql_query($sql, $connection);

$row = mysql_fetch_object($result);

 $resultentry["timeIn_one"] = substr($row->timeIn_one,0,strlen($row->timeIn_one)-3);
 	 $resultentry["timeOut_one"] = substr($row->timeOut_one,0,strlen($row->timeOut_one)-3);
 	 $resultentry["timeIn_two"] = substr($row->timeIn_two,0,strlen($row->timeIn_two)-3);
 $resultentry["timeOut_two"] = substr($row->timeOut_two,0,strlen($row->timeOut_two)-3);

 	  printf("<input type=hidden name=rowid value='%s'>", $rowid);

  printf("Time In:<br>
  	<input type=time name=timeIn_one value='%s'><br><br>
  ", $resultentry["timeIn_one"]);
  printf("Time Out:<br>
  	<input type=time name=timeOut_one value='%s'><br><br>
  ", $resultentry["timeOut_one"]);
  printf("Time In:<br>
  	<input type=time name=timeIn_two value='%s'><br><br>
  ", $resultentry["timeIn_two"]);
  printf("Time Out:<br>
  	<input type=time name=timeOut_two value='%s'><br><br>
  ", $resultentry["timeOut_two"]);

  printf("<input type=Submit value=Update time>");
  printf("</form");

mysql_free_result($result);

?>
<br>
<br><br>
<a href="dummyview.php3">Cancel</a>
</form>
</center>
</body>

// Update timesheet page

$update = "Update st set timeIn_one='${timeIn_one}:00', timeOut_one='${timeOut_one}:00', timeIn_two='${timeIn_two}:00', timeOut_two='${timeOut_two}:00', formatted_time='${formatted_time}:00' where rowid=$rowid";

$connection = mysql_connect("localhost","univrel","univrel") or die ("Couldn't connect to server.");

$db = mysql_select_db("univrel",$connection) or die ("Couldn't connect to database.");

$sql_result = mysql_query($update, $connection);

header ("location: dummyview.php3");
?>

Whatcha think?

Anon

In addition, look at this url to check how it looks:

http://www.ur.msu.edu/workshop/artice/dummyview.php3

[deleted]

Yes...

I see what you are doing. You have to calculate the total hours AFTER you get the values for $timeOne_in, etc.

Copy the following code to the dummyview.php3:-

<body>
<center>
<h1>Student Timesheet</h1>
<?
$sql = "SELECT * from st";
If ($date)
$sql .= "name like '%$date%' and ";
If ($timeIn_one)
$sql .= "email like '%$timeIn_one%' and ";
If ($timeOut_one)
$sql .= "password like '%$timeOut_one%' and ";
If ($timeIn_two)
$sql .= "department like '%$timeIn_two%' and ";
If ($timeOut_two)
$sql .= "department like '%$timeOut_two%' and ";
If ($tot_hrs_day)
$sql .= "department like '%$tot_hrs_day%' and ";

$connection = mysql_connect("localhost","univrel","univrel") or die ("Couldn't connect to server.");
$db = mysql_select_db("univrel",$connection) or die ("Couldn't connect to database.");
$sql_result = mysql_query($sql,$connection);

<table border=\"1\" cellspacing=\"5\" cellpadding=\"3\" width=\"50%\">
<tr>
<td align="center"><b>Date</b></td>
<td align="center"><b>Time In:</b></td>
<td align="center"><b>Time Out:</b></td>
<td align="center"><b>Time In:</b></td>
<td align="center"><b>Time Out:</b></td>
<td align="center"><b>Total Time of Day</b></td>
</tr>
<?
while (($row = mysql_fetch_object($sql_result)))
{
$tarray = explode(":", $row->timeIn_one);
$tone_in = ($tarray[0]60) + $tarray[1];
$tarray = explode(":", $row->timeOut_one);
$tone_out = ($tarray[0]60) + $tarray[1];

$total_timeOne = $tone_out - $tone_in; 

$tarray = explode(":", $row->timeIn_two); 
$ttwo_in = ($tarray[0]*60) + $tarray[1]; 
$tarray = explode(":", $row->timeOut_two); 
$ttwo_out = ($tarray[0]*60) + $tarray[1]; 

$total_timeTwo = $ttwo_out - $ttwo_in; 

$totaltime = $total_timeOne + $total_timeTwo;
$hours = floor($totaltime/60); 
$minutes = $totaltime%60; 

$formatted_time = "$hours:$minutes";

printf("<tr>
<td><center>%s</center></td>
<td><center>%s</center></td>
<td><center>%s</center></td>
<td><center>%s</center></td>
<td><center>%s</center></td>
<td><center>$formatted_time </center></td>
<td><a href=\"dummylogin.php3?rowid=%s\"><I>Log time</I></a></td>
</tr>\n", $row->date,
substr($row->timeIn_one,0,strlen($row->timeIn_one)-3),
substr($row->timeOut_one,0,strlen($row->timeOut_one)-3),
substr($row->timeIn_two,0,strlen($row->timeIn_two)-3),
substr($row->timeOut_two,0,strlen($row->timeOut_two)-3),
substr($row->formatted_time,0,strlen($row->formatted_time)-3),
$row->rowid);

}
printf("</table>\n");
mysql_free_result($sql_result);

?>
</form>
</center>
</body>

I'm hope it works this time :-)
If it doesn't, send me a copy of your database entries (either a SQL dump or you can tell me field names and sample values). I'll test out the whole thing completely on my system and mail you the working code.

-sridhar

[deleted]

Artice,

In the code I gave you,
change the following:

On the final part where you do printf and print out the times and the formatted time, change:

substr($row->formatted_time,0,strlen($row->formatted_time)-3)

to simply $formatted_time

-sridhar

Anon

Wonderful Sridhar,

Its calculating good now, thanks a grand. But something is wrong with the Log time page. When i click 'Log time' from the dummyview.php3 page, i receive an abundance of errors about $row is not an object! Why is that?? I am calling the right fields right? The plan is to retrieve the times out to another page to login and then return back to the view page for calculation. Check it out this code and URL...

// login time code

$sql = "select * from st where rowid=$rowid";

$connection = mysql_connect("localhost","univrel","univrel") or die ("Couldn't connect to server.");

$db = mysql_select_db("univrel",$connection) or die ("Couldn't connect to database.");

$result = mysql_query($sql, $connection);

$row = mysql_fetch_object($result);

 $resultentry["timeIn_one"] = substr($row->timeIn_one,0,strlen($row->timeIn_one)-3);
 	 $resultentry["timeOut_one"] = substr($row->timeOut_one,0,strlen($row->timeOut_one)-3);
 	 $resultentry["timeIn_two"] = substr($row->timeIn_two,0,strlen($row->timeIn_two)-3);
 $resultentry["timeOut_two"] = substr($row->timeOut_two,0,strlen($row->timeOut_two)-3);

 	  printf("<input type=hidden name=rowid value='%s'>", $rowid);

  printf("Time In:<br>
  	<input type=time name=timeIn_one value='%s'><br><br>
  ", $resultentry["timeIn_one"]);
  printf("Time Out:<br>
  	<input type=time name=timeOut_one value='%s'><br><br>
  ", $resultentry["timeOut_one"]);
  printf("Time In:<br>
  	<input type=time name=timeIn_two value='%s'><br><br>
  ", $resultentry["timeIn_two"]);
  printf("Time Out:<br>
  	<input type=time name=timeOut_two value='%s'><br><br>
  ", $resultentry["timeOut_two"]);

  printf("<input type=Submit value=Update time>");
  printf("</form");

mysql_free_result($result);

?>
<br>
<br><br>
<a href="dummyview.php3">Cancel</a>
</form>
</center>
</body>

// URL for Log time page

http://www.ur.msu.edu/workshop/artice/dummylogin.php3

Whatcha think?

Anon

did you receive the last message??

[deleted]

Artice,

Your $rowid is invalid. Do you have a primary key or row number field in your table?

Look at the page calling dummylogin and look at the form value rowid=xxx. xxx is invalid as it doesn't look like a proper id

-sridhar

Anon

Hi Sridhar,

How have you been? I'm doing okay. I left out a '%s' line so thats why my rowid was not communicating right.

My timesheet is up and running. Check it out at and play with it (if you would like)
www.ur.msu.edu/workshop/artice/dummyview.php3.

Hey sridhar i have a question. I want to loop the pay-period dates in php. So for instance, after the March 14, 2001 the dates should loop to the next 14 days with a clear timesheet(March 15, 2001...etc.)instead of typing out 14 row dates again. How can i code this? I am now in the process of finding date functions that would allow this. Also when the students login, i will match up the student's rowid with the dateId from my date table. So to conclude this matter, i want the student to retrieve their timesheet from the date table when they login in, without having to build separate tables for each student. Is this possible??

// Timesheet login code page

<?
$sql = "select * from stud where name='$name' and password='$password'" ;

$connection = mysql_connect("localhost","univrel","univrel") or die ("Couldn't connect to server.");

$db = mysql_select_db("univrel",$connection) or die ("Couldn't connect to database.");

$sql_result = mysql_query($sql, $connection);

If (mysql_num_rows($sql_result))
{
header("Location: http://www.ur.msu.edu/workshop/artice/dummyview.php3");
}

else
{
echo("LOGIN FAILED!! Try again please!");
}
?>

watcha think?