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

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

回答2:

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

回答3:

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

回答4:

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

回答5:

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