CODEWITHSUNDEEP
phpmysql
khrisdaniels
khrisdaniels

Reputation: 79

MYSQL SELECT query to merge output in single row on table

I have a problem regarding my class attendance report with a dynamic range of date. So far, I am able to get to display the dates between two dates and make it as a column. My problem now is the row of data to display. In my query, if a multiple dates have a data inserted in my attendance table with the same student ID it also outputs the same ID in my query in a different row.

Here's my query:

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

And this is what I did to output it in a HTML table/datatables:

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

Here is the output:

enter image description here

So as you can see, there is different row of data of each dates of the same student. What I want is to merge them in a single row. I think it has something to do with my query or the way I output it. I hope someone could help me.

Upvotes: 1

Views: 91

Answers (1)

khrisdaniels
khrisdaniels

Reputation: 79

UPDATE: This is for someone who will encounter the same problem with me in the future.

I was able to solve my problem by putting COALESCE inside a MAX function.

Here it is:

$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));

Upvotes: 1

Related Questions