PHP select timediff with select query and group by user

Question

i have this SQL Query:

$sql="SELECT *, COUNT(assigned_to) AS my_groupcount from tickets where deleted = '' and DAY(datetime) = '".$_GET["d"]."' and MONTH(datetime) = '".$_GET["m"]."' and YEAR(datetime) = '".$_GET["Y"]."' group by assigned_to order by datetime ASC ";
    $rs=mysql_query($sql,$conn);
    while($result=mysql_fetch_array($rs))
    {
        //work out the total time taken
        $sql3="SELECT *,  TIMEDIFF( timeend, timestart ) AS support_time_used FROM ticket_updates WHERE ticket_seq = '".$result["ticketnumber"]."' ";
        $rs3=mysql_query($sql3,$conn) or die (mysql_error());
        $totaltime = 0;
        while($result3=mysql_fetch_array($rs3))
        {
            $totaltime = $totaltime+substr($result3["support_time_used"],0,2)*60+substr($result3["support_time_used"],3,2);
        }   
        $hours=intval($totaltime / 60);
        $minutes=$totaltime -($hours * 60);
        $total_ticket_time = $hours.'h '.$minutes.'m';

        echo $result["assigned_to"].' ('.$result["my_groupcount"].') - Time: '.$total_ticket_time.'<br>';
    }

so its selecting all the rows from the tickets table and grouping by the asigned_to column.

it then works out the time used for each user from the ticket_updates table.

the ticket_updates.ticket_seq column links to the tickets.ticketnumber column and there may be just one or multiple rows in the ticket_updates table for 1 row in the tickets table so it adds all the differences up in the ticket_updates table.

im trying to list the assigned_to (one each) from the tickets table and put next to each one how much time they have used but its only selecting it form one ticket.

how can i make it select from all the tickets?

AdrianBR · Accepted Answer · 2013-09-10T08:52:04.330

1

you don't need to do it in two steps, do you.

select assigned_to, sum(TIMEDIFF( timeend, timestart )) as total_time, count(distinct ticket_id) as ticket_count from ticket_update where ticket_id in (select ticket_id from ticket where condition)
group by assigned_to

edit: you actually need:

        select ticket.assigned_to, 
sum(TIMEDIFF( ticket_update_timeend, ticket_update.timestart )) as total_time, count(distinct ticket.ticket_id) as ticket_count 
from 
    ticket_update 
    inner join ticket
    on ticket.ticket_id=ticket_update.ticket_id
where ticket.deleted = '' and DAY(ticket.datetime) = '".$_GET["d"]."' and MONTH(ticket.datetime) = '".$_GET["m"]."' and YEAR(ticket.datetime) = '".$_GET["Y"]."'
    group by ticket.assigned_to

edited Sep 10 '13 at 08:52

answered Sep 10 '13 at 08:31

AdrianBR

2,762
1
15
29

which query needs to be replaced by this? – user2710234 Sep 10 '13 at 08:36
query replaces the two queries and the first while. You just need to echo the output – AdrianBR Sep 10 '13 at 09:17

PHP select timediff with select query and group by user

1 Answers1