PHP Sum a value in while loop, but with conditions

Question

I have two tables to be joined, 1 is user and 1 is attendance.

TABLE : attendance
id   userId   totalHours 
1    1        0745
2    3        0845
3    1        0945

TABLE : user
id   name  departmentId
1    John  2
2    Sean  2
3    Allan 2

Not every user have attendance record (their totalHours) But I need to query by userId WHERE departmentId = XXXX and SUM each of their totalHours that exist, without neglecting the userId without any record in attendance.

So far I made this:

$result = mysqli_query($con,"SELECT * FROM user WHERE departmentId = 2");
while($row = mysqli_fetch_array($result))
{
  $id = $row['userId'];
  $result2 = mysqli_query($con,"SELECT * FROM attendance WHERE userId = $id");
  while($row2 = mysqli_fetch_array($result2))
  $totalHours = 0;
  {
     $totalHours = $row2['totalHours'];
     $grandTotal += $totalHours;
     $totalHoursInHHmm = substr_replace($totalHours,":",2,0); 
     $parsed = date_parse($totalHoursInHHmm); 
     $toSeconds = $parsed['hour'] * 3600 + $parsed['minute'] * 60;
     $total += $toSeconds;
     $init = $total;
     $hours = floor($init / 3600);
     $minutes = floor(($init / 60) % 60);    
  }
  echo "$hours:$minutes";
}

The result shows all the user in the department, and did SUM all the totalHours for each userId , but what was wrong is, there are userId without any attendance still have the SUM value shown, inheriting previous total Sum

Any help is appreciated :)

can you post the schema of the tables involved. You'll want to do a join or left join group by and sum() maybe, how are user table and attendance table related — ArtisticPhoenix, Jul 07 '14 at 01:29
In the second while loop, I *think* you mean to use $totalHours = $row2['totalHours'] And, I would set $grandTotal to 0 before the first while loop starts. — SW_user2953243, Jul 07 '14 at 01:31
the OP is asking how to exclude those users that did not attend from being counted Ie. he needs to join the user table to the attendance table to get the proper result set to begin with. — ArtisticPhoenix, Jul 07 '14 at 01:32
Artisitic is right, and SW_user2953243 also right, i made changes to $row to $row2 — Ahlan Wasahlan Anta, Jul 07 '14 at 01:35
Although not necessarily answering your question it is worth pointing out a potential issue with this approach. If you have 30 students you will be running 31 queries (students plus 1)). Not necessarily a problem if you only have a few students but will become a problem if this number grows a lot. You can also use SQL to calculate the total hours. BTW you have "$totalHours = 0;" between the while loop and the intended body of the while loop. Due to this total hours will not be calculated properly. — Peter, Jul 07 '14 at 01:42

FuzzyTree · Accepted Answer · 2014-07-07T02:02:51.927

1

I need to query by userId WHERE departmentId = XXXX and SUM each of their totalHours that exist, without neglecting the userId without any record in attendance.

To show the hours for all users in a given department, even users w/o rows in the attendance table, use a LEFT JOIN

Use (CAST(totalHours AS UNSIGNED) % 100)/60 + FLOOR(CAST(totalHours AS UNSIGNED)/100) to convert your varchar hours+minutes to a single number of hours.

$query = "SELECT u.id, 
SUM((CAST(totalHours AS UNSIGNED) % 100)/60 + FLOOR(CAST(totalHours AS UNSIGNED)/100)) grandTotal
FROM user u
LEFT JOIN attendance a
ON u.id = a.userId
WHERE u.departmentId = 2
GROUP BY u.id";

$result = mysqli_query($con,$query);

while($row = mysqli_fetch_array($result)) {
    print $row['id'] . ' ' . $row['grandTotal'];
}

edited Jul 07 '14 at 02:02

answered Jul 07 '14 at 01:35

FuzzyTree

32,014
3
54
85

Op wants only those with total hours. "but what was wrong is, there are userId without any attendance" – ArtisticPhoenix Jul 07 '14 at 01:36
@ArtisiticPhoenix `without neglecting the userId without any record in attendance` indicates that he also wants those without total hours – FuzzyTree Jul 07 '14 at 01:37
your right, the question is confusing, we both agree the OP is better off with a JOIN and GROUP with sum though. is u.id not u.userId though he posted the other table now. – ArtisticPhoenix Jul 07 '14 at 01:40
1

This does the trick! thank you FuzzyTree. Your help do make my day, i have been stuck with this with my poor database planning. – Ahlan Wasahlan Anta Jul 07 '14 at 02:12
And thank you to everyone who helps, hope I can repay everyone kindness and time spent – Ahlan Wasahlan Anta Jul 07 '14 at 02:28

score 1 · Answer 2 · edited May 23 '17 at 12:20

1

try this, just in the first while you wont need both.

SELECT TIME_FORMAT(sum(STR_TO_DATE(a.totalHours, '%i')),'%H:%i') as sum, u.id, u.name FROM user AS u LEFT JOIN attendance AS a ON a.userId = u.id WHERE u.departmentId = 2 AND u.id = $user_id GROUP by u.id;

Update, try that not sure if it will work I cant test it right now but refer to this question.

how to convert weird varchar "time" to real time in mysql?

Once you get the right query working it will be really easy in php to do the rest. The DB should do this work, although the schema is not ideal here..

edited May 23 '17 at 12:20

Community

1
1

answered Jul 07 '14 at 01:36

ArtisticPhoenix

21,464
2
24
38

Hi ArtisticPhoenix, i can't SUM() the column as the data is in time format without colon, I need to sum in in the loop as i need to convert it to seconds and add it from there. Any idea? – Ahlan Wasahlan Anta Jul 07 '14 at 01:38
you can still sum it you will have to take the first 2 digits the hours and convert them to minutes and add to the second two then sum it? that might work. what is the field type for that row? – ArtisticPhoenix Jul 07 '14 at 01:44
Artisitic, it is varchar(50) – Ahlan Wasahlan Anta Jul 07 '14 at 01:46

Valentoni · Answer 3 · 2014-07-07T02:02:20.500

1

OK! It's happening because, the users that doesn't have any attendance isn't passing through the second while, then the values aren't being restarted. You can correct this simply setting $grandTotal after you echo it. Like this:

$result = mysqli_query($con,"SELECT * FROM user WHERE departmentId = 2");
while($row = mysqli_fetch_array($result))
{
  $id = $row['userId'];
  $result2 = mysqli_query($con,"SELECT * FROM attendance WHERE userId = $id");
  while($row2 = mysqli_fetch_array($result2))
  {
     $totalHours = 0;
     $totalHours = $row2['totalHours'];
     $grandTotal += $totalHours      
  }
  echo $grandTotal;
  $grandTotal = 0;
}

edited Jul 07 '14 at 02:02

answered Jul 07 '14 at 01:39

Valentoni

308
4
19

within the second while loop, he should use $row2, not $row. – SW_user2953243 Jul 07 '14 at 01:43
This will not work properly. "$totalHours = 0" is the body of the while loop as it is misplaced – Peter Jul 07 '14 at 01:43
Yes, i di not noticed that. The "$totalHours = 0" is out of the while loop range. He must move that within it. I already corrected my example, including the "$row2 VS. $row" issue. – Valentoni Jul 07 '14 at 02:01

Mohit S · Answer 4 · 2014-07-07T01:45:11.087

0

What I understood from the question is NOT to neglect those userid even if they do not have their attandance record. In this scenario I have 2 Options to be chosen ...

1.

$result = mysqli_query($con,"SELECT * FROM user WHERE departmentId = 2");
while($row = mysqli_fetch_array($result))
{
  $id = $row['userId'];
  $result2 = mysqli_query($con,"SELECT * FROM attendance WHERE userId = $id");
  $grandTotal=0;
  while($row2 = mysqli_fetch_array($result2))
  $totalHours = 0;
  {
     $totalHours = $row2['totalHours'];
     $grandTotal += $totalHours      
  }
  echo $grandTotal;
}

2.

$result = mysqli_query($con,"SELECT * FROM user WHERE departmentId = 2");
while($row = mysqli_fetch_array($result))
{
  $id = $row['userId'];
  $result2 = mysqli_query($con,"SELECT * FROM attendance WHERE userId = $id");
  while($row2 = mysqli_fetch_array($result2))
  $totalHours = 0;
  {
     $totalHours = $row2['totalHours'];
     if($totalHours<=0)
    $grandTotal=0;
     $grandTotal += $totalHours      
  }
  echo $grandTotal;
}

edited Jul 07 '14 at 01:45

answered Jul 07 '14 at 01:39

Mohit S

13,723
6
34
69

1

the 2nd while loop is not going to work. The body is "$totalHours = 0" as it is placed between the declaration of the loop and the intended body. – Peter Jul 07 '14 at 01:46
@Peter I thought the same things but than OP has the same code so I wrote it ... the same for him .. but I do agree with u :) – Mohit S Jul 07 '14 at 01:48
Tried but returned 0 :( – Ahlan Wasahlan Anta Jul 07 '14 at 02:18
I am sure that I was not able to understand your question than ... bcoz i was expecting it to return 0 and it is returning that. – Mohit S Jul 07 '14 at 02:20

score 0 · Answer 5 · answered Jul 07 '14 at 01:46

0

Move $totalhours = 0 within the curly braces {}.
Set $hours = $minutes = 0 at the top of the second while loop (where you set $totalhours = 0) **If you don't reset $hours and $minutes, users who don't have attendance will get the old values.

answered Jul 07 '14 at 01:46

SW_user2953243

334
1
12

PHP Sum a value in while loop, but with conditions

5 Answers5

Linked