CODEWITHSUNDEEP
phpmysqlarraysaddition
jonthoughtit
jonthoughtit

Reputation: 165

For each result, add/combine for one single result MySql

I'm able to get the following results with this working query:

$query = "SELECT tt.time_start, tt.time_end
FROM track_time as tt, project_track as pt, project_clients as pc, clients as cl
WHERE tt.track_id = pt.track_id
AND pt.project_id = pc.project_id
AND pc.client_id = cl.client_id
AND cl.client_id =".$db->prep($client_id);
$res = $db->query($query,'assoc');

**RESULTS**
time_start  time_end    client_id
1340927940  1340928420  1
1341066240  1341077040  1
1341086400  1341088560  1
1340954400  1340986800  7

What I'm trying to achieve for each client is to add up all of their time_start values for one result, add up all of their time_end values for one result, and then get the difference between those two results. This will allow me to determine the totals for each client.

I'm working in PHP and trying all ways of foreach with no luck and incorrect array returns. Any ideas?

Upvotes: 0

Views: 104

Answers (2)

John Woo
John Woo

Reputation: 263803

Try this:

$query = "SELECT pc.client_id, 
                 (SUM(tt.time_start) - SUM(tt.time_end)) as `Difference`
          FROM  track_time as tt, project_track as pt, 
                project_clients as pc, clients as cl
          WHERE tt.track_id = pt.track_id
                    AND pt.project_id = pc.project_id
                    AND pc.client_id = cl.client_id
                    AND cl.client_id =" . $db->prep($client_id) . " 
          GROUP BY client_id";

Upvotes: 1

aacanakin
aacanakin

Reputation: 2913

time_start and time_end seems to be timestamp values. So ;

$total = 0;
foreach( $array as $item)
{
     $total += strtotime( $item['time_start']);
}

after that, if you run a date() function in intented format, I think it should work correctly.

Upvotes: 0

Related Questions