CODEWITHSUNDEEP
phploopswhile-loopsum
Ahlan Wasahlan Anta
Ahlan Wasahlan Anta

Reputation: 33

PHP Sum a value in while loop, but with conditions

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

Upvotes: 3

Views: 1685

Answers (5)

FuzzyTree
FuzzyTree

Reputation: 32392

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'];
}

Upvotes: 1

Valentoni
Valentoni

Reputation: 328

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;
}

Upvotes: 1

ArtisticPhoenix
ArtisticPhoenix

Reputation: 21661

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..

Upvotes: 1

SW_user2953243
SW_user2953243

Reputation: 354

  • 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.

Upvotes: 0

Mohit S
Mohit S

Reputation: 14044

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;
}

Upvotes: 0

Related Questions