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
– ArtisticPhoenix, Commented Jul 7, 2014 at 1: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
– SW_user2953243, Commented Jul 7, 2014 at 1: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
– ArtisticPhoenix, Commented Jul 7, 2014 at 1:32

FuzzyTree · Accepted Answer · 2014-07-07 02:02:51Z

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 7, 2014 at 2:02

answered Jul 7, 2014 at 1:35

FuzzyTree

32.4k3 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ArtisticPhoenix Over a year ago

Op wants only those with total hours. "but what was wrong is, there are userId without any attendance"

FuzzyTree Over a year ago

@ArtisiticPhoenix without neglecting the userId without any record in attendance indicates that he also wants those without total hours

ArtisticPhoenix Over a year ago

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.

Ahlan Wasahlan Anta Over a year ago

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 Over a year ago

And thank you to everyone who helps, hope I can repay everyone kindness and time spent

Community · Accepted Answer · 2017-05-23 12:20:45Z

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, 2017 at 12:20

CommunityBot

11 silver badge

answered Jul 7, 2014 at 1:36

ArtisticPhoenix

21.7k2 gold badges27 silver badges38 bronze badges

3 Comments

Ahlan Wasahlan Anta Over a year ago

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?

ArtisticPhoenix Over a year ago

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?

Ahlan Wasahlan Anta Over a year ago

Artisitic, it is varchar(50)

Valentoni · Accepted Answer · 2014-07-07 02:02:20Z

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 7, 2014 at 2:02

answered Jul 7, 2014 at 1:39

Valentoni

3284 silver badges19 bronze badges

3 Comments

SW_user2953243 Over a year ago

within the second while loop, he should use $row2, not $row.

Peter Over a year ago

This will not work properly. "$totalHours = 0" is the body of the while loop as it is misplaced

Valentoni Over a year ago

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.

Mohit S · Accepted Answer · 2014-07-07 01:45:11Z

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 7, 2014 at 1:45

answered Jul 7, 2014 at 1:39

Mohit S

14.1k6 gold badges36 silver badges73 bronze badges

4 Comments

Peter Over a year ago

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.

Mohit S Over a year ago

@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 :)

Ahlan Wasahlan Anta Over a year ago

Tried but returned 0 :(

Mohit S Over a year ago

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.

SW_user2953243 · Accepted Answer · 2014-07-07 01:46:48Z

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 7, 2014 at 1:46

SW_user2953243

3541 silver badge12 bronze badges

Collectives™ on Stack Overflow

PHP Sum a value in while loop, but with conditions

5 Answers 5

5 Comments

3 Comments

3 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

3 Comments

3 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related