php - MYSQL SELECT 查询将输出合并到表上的单行中

Question

我的类出勤报告存在动态日期范围问题。到目前为止，我能够显示两个日期之间的日期并将其作为一列。我现在的问题是要显示的数据行。在我的查询中，如果多个日期在我的出勤表中插入了具有相同学生 ID 的数据，它也会在我的查询中的不同行中输出相同的 ID。

这是我的查询:

$startDate = date_format(new DateTime($_POST['from']),"Y-m-d");
$endDate = date_format(new DateTime($_POST['to']),"Y-m-d");
$section = $_POST['section'];
$subject = $_POST['subject'];
global $request;

$step1 = mysqli_query($connect,"SET @sql = NULL;") or die(mysqli_error());
$step2 = mysqli_query($connect,"SET SESSION group_concat_max_len = 1000000;") or die(mysqli_error());
$query1 = mysqli_query($connect,"SELECT GROUP_CONCAT(DISTINCT
    CONCAT(
      'COALESCE((CASE WHEN tbl_subjectattendance.Date = ''',
      date_format(date, '%Y-%m-%d'),
      ''' THEN tbl_subjectattendance.status END), '''') AS `',
      date_format(date, '%Y-%m-%d'), '`'
    )
  ) INTO @sql
FROM calendar
where date>='$startDate'
  and date <='$endDate'") or die(mysqli_error($connect));

$step3 = mysqli_query($connect,"SET @sql = CONCAT('SELECT DISTINCT tbl_subjectattendance.student_id, tbl_student.last_name, ', @sql, ' 
            FROM
                tbl_subjectattendance, tbl_student
            WHERE
                tbl_subjectattendance.section_id = ''$section''
            AND
                tbl_subjectattendance.subj_id = ''$subject''
            AND 
                tbl_subjectattendance.student_id = tbl_student.student_id')") or die(mysqli_error($connect));

$query = mysqli_query($connect,"SELECT @sql;") or die(mysqli_error());

while($row = mysqli_fetch_array($query,MYSQLI_ASSOC)){
    $request = $row['@sql'];
}

这就是我将其输出到 HTML 表/数据表中的方法:

<?php 
    $result = mysqli_query($connect, "$request") or die(mysqli_error($connect));
?>
<table class="table table-hover table-bordered" id="report" style="background-color:white;">
   <thead>
         <tr>
  <?php 
   $row = mysqli_fetch_assoc($result);
    foreach ($row as $col => $value) {
            echo "<th>".$col."</th>";
         }
  ?>
         </tr>
  </thead>
  <tbody>
 <?php
         mysqli_data_seek($result, 0);
         while ($row = mysqli_fetch_assoc($result)) {
 ?>
 <tr>
 <?php         
        foreach($row as $key => $value){
               echo "<td>".$value."</td>";
              }
 ?>
 </tr>
 <?php } ?>
 </tbody>
 </table>

这是输出:

如您所见，同一学生的每个日期都有不同行的数据。我想要的是将它们合并在一行中。我认为这与我的查询或我输出它的方式有关。我希望有人能帮助我。

Answer 1

更新:这是为了将来会遇到与我相同问题的人。

通过将 COALESCE 放入 MAX 函数中，我能够解决我的问题。

这里是:

$query1 = mysqli_query($connect,"SELECT GROUP_CONCAT(DISTINCT
CONCAT(
  'MAX(COALESCE((CASE WHEN tbl_subjectattendance.Date = ''',
  date_format(date, '%Y-%m-%d'),
  ''' THEN tbl_subjectattendance.status END), '''')) AS `',
  date_format(date, '%Y-%m-%d'), '`'
  )
 ) INTO @sql
 FROM calendar
 WHERE date>='$startDate'
 AND date <='$endDate'") or die(mysqli_error($connect));