PHP在while循环中求和，但有条件 [英] PHP Sum a value in while loop, but with conditions

问题描述

我有两个要连接的表，1个是用户，1个是出勤.

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

不是每个用户都有出勤记录(他们的总时数) 但是我需要按userId查询WHERE departmentId = XXXX并求和它们存在的所有totalHours，而不能忽略没有任何出席记录的userId.

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

结果显示部门中的所有用户，并对每个userId的全部totalHours进行SUM运算，但是错误的是，仍然没有任何出席的userId仍然显示了SUM值，继承了以前的总Sum

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

我们将不胜感激:)

推荐答案

我需要通过userId来查询WHERE departmentId = XXXX并分别求和 他们存在的totalHours，而忽略了userId，没有任何 出席记录.

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.

要显示给定部门中所有用户的小时数，甚至不包含attendance表中行的用户，请使用LEFT JOIN

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

使用(CAST(totalHours AS UNSIGNED) % 100)/60 + FLOOR(CAST(totalHours AS UNSIGNED)/100)将您的varchar小时+分钟数转换为单个小时数.

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

这篇关于PHP在while循环中求和，但有条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！