time subtrating then adding

[stupidllama]

This is pretty general.

I have dataase the hold info like when someone start a project and when they finish a project, so the start and stop times are entered into the database via php. Once in there i have a reports section where you can view the project people worked on and from what time to what time. so there may be many entires, for example:

artist: time start: time stop: time worked:
tim 09:52:00 13:34:00 3:42
tim 08:00:00 08:20:00 0:20

and so on...

so the actuall times are stored in a db but a php function calculated the time worked on the fly as each row is displayed from the db.

what i need is a to add the time worked up and output that, in this case 4:02 at the very bottom. so basically the time worked is a php variable calcuated for each row in a while loop, im thinking somehow through a foreach i could sum array but Im not sure how to get each value in an array or if its possible.
any help is much appreciated

[NogDog]

It could be easily done via SQL:

SELECT SUM(time_worked) AS total_time FROM table_name WHERE artist='tim'

[stupidllama]

Except there is no "time_worked" section of the database, the only thing relevent in the database is name, time start, and time stop. then php calculates the difference and displays it. the value is calculated during the while loop as it displays each relevent entry for the artist selected, in this case "tim"

[leatherback]

select timediff(starttime-endtime) as time_worked, SUM(time_worked) AS total_time FROM table_name WHERE artist='tim'

[stupidllama]

interesting, thats just crazy enough to work

that and it makes sense.

for some reason i jsut could see the forest for the trees or something like that

[stupidllama]

that gives me

Unknown column 'time_worked' in 'field list'

something must be jsut a little off cause this seems like it whould work,
there is not a column named time_worked in the database but this doesnt apear to need one, am i right? its setting the time_worked value to be whatever the timediff is and then passing it along.

any way here is the code

<?php
//fuction that calculates time spent on project
function date_diff($d1, $d2){
$d1 = (is_string($d1) ? strtotime($d1) : $d1);
$d2 = (is_string($d2) ? strtotime($d2) : $d2);

$diff_secs = abs($d1 - $d2);
$base_year = min(date("Y", $d1), date("Y", $d2));

$diff = mktime(0, 0, $diff_secs, 1, 1, $base_year);
return array(
    "years" => date("Y", $diff) - $base_year,
    "months_total" => (date("Y", $diff) - $base_year) * 12 + date("n", $diff) - 1,
    "months" => date("n", $diff) - 1,
    "days_total" => floor($diff_secs / (3600 * 24)),
    "days" => date("j", $diff) - 1,
    "hours_total" => floor($diff_secs / 3600),
    "hours" => date("G", $diff),
    "minutes_total" => floor($diff_secs / 60),
    "minutes" => (int) date("i", $diff),
    "seconds_total" => $diff_secs,
    "seconds" => (int) date("s", $diff)
);

}
// function that coverts calculated time into pure hours and minutes format
function m2h($mins) {
if ($mins < 0) {
$min = Abs($mins);
} else {
$min = $mins;
}
$H = Floor($min / 60);
$M = ($min - ($H 60)) / 100;
$hours = $H + $M;
if ($mins < 0) {
$hours = $hours (-1);
}
$expl = explode(".", $hours);
$H = $expl[0];
if (empty($expl[1])) {
$expl[1] = 00;
}
$M = $expl[1];
if (strlen($M) < 2) {
$M = $M . 0;
}
$hours = $H . ":" . $M;
return $hours;
}

$xartist=$POST['artist'];
//sets wheather there is a date range or not and querys appropriatly
if ($xartist==''){
$xartist=$GET['artist'];
}else{
$xartist = $xartist;
}
//retrieves artist and date range if aplicable
$xday=$POST['day'];
$xmonth=$POST['month'];
$xyear=$POST['year'];
$xdate= $xyear."-".$xmonth."-".$xday;
$xday2=$POST['day2'];
$xmonth2=$POST['month2'];
$xyear2=$POST['year2'];
$xdate2= $xyear2."-".$xmonth2."-".$xday2;
//execute query

if ($xdate=='--'){
$query = "SELECT FROM arttracker WHERE artist= '$xartist' ORDER BY date";
}else{
$query = "SELECT FROM arttracker WHERE date BETWEEN '$xdate' and '$xdate2' AND artist = '$xartist' ORDER BY date";
}

$result = mysql_query($query) or die("Error: " . mysql_error());
$num=mysql_num_rows($result);
$i=0;
//loop to display results
while ($i < $num) {
$id=mysql_result($result,$i,"id");
$date=mysql_result($result,$i,"date");
$timein=mysql_result($result,$i,"timein");
$timeout=mysql_result($result,$i,"timeout");
$work=mysql_result($result,$i,"work");
$billing=mysql_result($result,$i,"billing");
$artist=mysql_result($result,$i,"artist");
$comments=mysql_result($result,$i,"comments");

//alternates row color
$color1 = "#cccccc";
$color2 = "#ffffff";
$row_color = ($i % 2) ? $color1 : $color2;
//executes fuction that calculates total time worked
$totaltime = date_diff($timeout, $timein);
$time=($totaltime['minutes_total']);
//converts time worked from total minutes to hours and minutes format(3:33)
$timediff=m2h($time);

//displays rows
echo "<table cellpadding=4 cellspacing=0 bgcolor=$row_color>";
echo "<tr>";
echo "<td align=right width=5%></td>";
echo "<td width=10% class=admininfo>$artist</td>";
echo "<td width=10% class=admininfo>$date</td>";
echo "<td width=10% class=admininfo>$timein</td>";
echo "<td width=10% class=admininfo>$timeout</td>";
echo "<td width=10% class=admininfo>";
echo $timediff;
echo "</td>";
echo "<td width=10% class=admininfo>$work</td>";
echo "<td width=10% class=admininfo>$billing</td>";
echo "<td width=20% class=admininfo>$comments</td>";
echo "<td width=5%></td>";
echo "</tr>";
echo "</table>";

$i++;

}

?>
<fieldset><legend><font size="-1">Total Time</font></legend>
<table cellpadding="0" cellspacing="0">
<tr>
<td height="5"><font size="-1">
<?
//clearly this is where the total time worked would go

?>[/QUOTE]

[NogDog]

Maybe you have to do something like:

SELECT SUM(TIMEDIFF(starttime - endtime)) AS time_worked . . .

?

[stupidllama]

now im outputting

Resource id #4

witch is usually an array correct?

[Kudose]

stupidllama wrote:

now im outputting

Resource id #4

witch is usually an array correct?

No, it is the resource identifier converted to a string on output.

Can you post your most recent code within the [noparse]

[/noparse] tags?

[stupidllama]

if you need more let me know

<?
//clearly this is where the total time worked on each project would go
$query2 = "SELECT SUM(TIMEDIFF(timeout,timein)) FROM arttracker WHERE artist = '$artist'";
$results2 = mysql_query($query2)or die (mysql_error());
echo $results2;

?>[/QUOTE]

when i used the - in the timediff() i kept getting an syntx error

the rest of the code is above and hast changed since it is workin fine

[Kudose]

You need to fetch the data

<?php
  $query2 = mysql_query("SELECT SUM(TIMEDIFF(timeout,timein)) AS `total_time` FROM arttracker WHERE artist = '$artist'") or die(mysql_error());
  $results2 = mysql_fetch_assoc($query2) or die (mysql_error());
  echo $results2['total_time'];
?>

[leatherback]

stupidllama wrote:

when i used the - in the timediff() i kept getting an syntx error

the rest of the code is above and hast changed since it is workin fine

My bad. timediff compares two colums, and returns the difference, colums separated by a comma. I was just thinking about the order in which they went, and type a minus instead of a comma.

If you run into these kind of trouble, it is always effective to quickly check the manual. Easy to do: php.net/FUNCTION_NAME will bring you to the manual, section FUNCTION_NAME

[Kudose]

Most of the time it works for MySQL functions as well.

i.e. mysql.com/timediff

Unfortunately MySQL puts a lot of functions on the same page, so you will have to scroll.

[stupidllama]

well its clearly not giving me the result im looking for.

its outputting 3620.0000

which if rounded and drop the zero give me 362

the two entries i have for test have a differences of 3:42 and 0:20
thus the 362 which should b 4:02 but its just not doing what i need it to do

i guess i have to figure out somether way around it.

seem like the SUM is treating time as time but rather just numbers witch makes sense,

thanks anyway for the help

[stupidllama]

My bad. timediff compares two colums, and returns the difference, colums separated by a comma. I was just thinking about the order in which they went, and type a minus instead of a comma.

ya i had figured that and did look it up at phpnet and used the comma and at least at that point was sure the syntax was correct but this doesnt seem to be working for me

but you guys (err...people) are definitly leading me to the promis land though after some slight modification

[QUOTE]<?php

$query2 = mysql_query("SELECT TIMEDIFF(timeout,timein) AS total_time FROM arttracker WHERE artist = '$artist'") or die(mysql_error());
$num2=mysql_num_rows($query2);
$r=0;
//loop to display results
while ($r < $num2) {
$tottime=mysql_result($query2,$r,"total_time");
echo $tottime."<br>";
$r++;
}
?> [/QUOTE]

and now have in my total time box

00:03:42
00:00:20

now i just have to add them up and im good to go.

sadly all of my bases are now belong to you

well actaully i dont know if this will work since alls im doing to echoing them in a loop it looks as though im right back where i started.

[stupidllama]

is there a way for getting each of the values produced into an array??

[stupidllama]

finally
this

<?php
$query2 = mysql_query("SELECT SUM(TIME_TO_SEC(TIMEDIFF(timeout,timein))) AS total_time FROM arttracker WHERE artist = '$artist'") or die(mysql_error());
$results2 = mysql_fetch_assoc($query2) or die (mysql_error());

$num2=mysql_num_rows($query2);
$r=0;
//loop to display results
while ($r < $num2) {
$tottime=mysql_result($query2,$r,"total_time");

$etime= ($tottime / 60);
echo m2h($etime);

$r++;
}

?>

gets me to

4:02

when my too outputs are 3:42 and 0:20

basically add the timediff up after it was converted to seconds, then divide by 60 to get minutes then run the minutes through my function to convert minutes into hours format. and walaaa.

i could have done it without you guys, i would by you all a beer but since i cant ill buy one for each of you and drink them myself