How to display data row wise in HTML table

[Account]

I have 3 tables in MySQL database
1)rep (id, userid, date)
2)time (time_id, id, Time)
3)stud_details (stid, time_id, sid, Student, Subject, Topic, Confidence)
One report has many time details and each time has many student details. I want to display the data row wise in HTML table as shown below.


As show in the screenshot, each time is displayed in column and for each time their respective student details are shown. Every column has different number of students.

I have below code which displays column wise data in HTML table. But the issue is wherever there is empty student data, rows are not displayed. So I need to display the rows for all the cells as shown in the above screenshot.

<?php
 
 $sql2 = 'SELECT rd.Time,rd.id from time as rd,  rep as rep WHERE rep.id=rd.id and 
          DateofReport=:date';      // Query to get all the Time of specific report

 $params = [':date'=>$model->DateofReport];    // Bind date parameter for the query

 $timedetails = \Yii::$app->db->createCommand($sql2, $params)->queryAll(); // Get all times
 
 ?>

 <?php
 echo "<table class='table table-bordered'>";

 foreach($timedetails as  $tdetails):
 
  ?>

  <td>
   <table class='table table-bordered'  style="border: 1px solid black">
      <tr  bgcolor='#B8B8B8' style="border: 1px solid black">
         <th style='border: 1px solid black;'>
            <?php echo $tdetails['Time']; // Display Time in columns?>  
        </th>
     </tr>

<?php
// Query to fetch student details for each time
$sql3 = 'SELECT s.StudentName, sd.Subject,sd.Topic,sd.Confidence 
         from time as rd, student as s, stud_details as sd, rep as rep
         where rd.time_id=sd.time_id
         and rep.id=rd.id
         and s.StudentId=sd.StudentId 
         and rd.id=:Id
         and rep.userid=:userid';

$params1 = [':userid' => $model->UserId];    // Bind user id parameter
$params1[':Id']=$tdetails['time_id'];        // Bind time parameter

$StudentDetails=\Yii::$app->db->createCommand($sql3, $params1)->queryAll();// Get student data
 
foreach($StudentDetails as $StudentDetails){
?>
  <tr>
       <td style='width:100px; word-wrap: break-word; border: 1px solid black;'>
           <b><?php echo $StudentDetails['StudentName'];?></b>
       </td>
  </tr>
  <tr>
       <td style='width:100px;word-wrap: break-word; border: 1px solid black;'>
               <?php echo  $StudentDetails['Subject'];?>
       </td>
 </tr>
 <tr>
       <td style='width:200px;word-wrap: break-word;border: 1px solid black;'>
              <?php echo  $StudentDetails['Topic'];?>
       </td>
 </tr>
 <tr>
       <td style='width:100px;word-wrap: break-word;border: 1px solid black;'> 
             <?php echo  $StudentDetails['Confidence'];?>
       </td>
 </tr>
 <?php
   }
 ?>
</table>
</td>
<?php
endforeach;
echo '</table>';
?>

[pbismad]

To do what you are asking will require that you pre-fetch all the data into an array variable, indexing/pivoting it using the time as the main array index, so that you can get the maximum count() per time slot. You should also use one single JOIN query.

A question. The first query does not use the userid to filter rows of data. If there is report data for more than one userid on the report date, you will get duplicate time values. Is this what you intend? Using one single JOIN query would address this problem, since you would only be matching data for the specific userid and dateofReport.

[Account]

pbismad
Yes, the first query uses the userid. A user has a report of their own for a particular date.

[pbismad]

Based on the queries in your code, the following single query should (untested) get the data that you want, in the order that you want it (most multi-row SELECT queries should have an ORDER BY term) -

$sql = "SELECT t.Time, s.StudentName, sd.Subject, sd.Topic, sd.Confidence
	FROM rep
	JOIN time t ON rep.id=t.id
	JOIN stud_details sd ON t.time_id=sd.time_id
	JOIN student s ON sd.StudentId=s.StudentId
	WHERE rep.DateofReport=:date AND rep.userid=:userid
	ORDER BY t.Time, s.StudentName, sd.Subject, sd.Topic";

Could you run this query and confirm that it does get the data that you need?

[Account]

pbismad
Yes, the query you suggested gets the data I want.

[sneakyimp]

If possible, it is usually better to fetch all your data first and only then start to output HTML code. It's bad to have all your PHP logic and HTML logic mixed up together. It's also bad, peformance-wise, to run queries in a loop if you can avoid it.

@pbismad makes a good point that you'll need to know the maximum number of rows that'll occur in any of your time slots before you output your first time slot.

[pbismad]

Account Yes, the query you suggested gets the data I want.

The yii database extension is PDO. The PDO::FETCH_GROUP fetch mode (added to the ->queryAll(...) method call) will index/pivot the data by the first column in the SELECT list (Time.) This will produce a data structure that you can get a maximum count() of the number of rows per time slot and loop over to produce the output.

To get the maximum count per time slot (assuming you fetched the data into $data) -

// get the maximum number of rows per time slot
$max = max(array_map('count',$data));

To produce the output -

// start the main table
?>
<table>
<tr>
<?php
// output the left label column
// 1st row is empty
?>
<td>
<table>
<tr><td>&nbsp;</td></tr>
<?php
foreach(range(1,$max) as $not_used)
{
	?>
	<tr><td>Student Name</td></tr>
	<tr><td>Subject</td></tr>
	<tr><td>Topic</td></tr>
	<tr><td>Confidence</td></tr>
	<?php
}
?>
</table>
</td>

<?php
// output each time column
foreach($data as $time=>$arr)
{
	?>
	<td>
	<table>
	<?php
	echo "<tr><th>$time</th></tr>";
	$extra = $max - count($arr);
	foreach($arr as $row)
	{
		echo "<tr><td><b>{$row['StudentName']}</b></td></tr>";
		echo "<tr><td>{$row['Subject']}</td></tr>";
		echo "<tr><td>{$row['Topic']}</td></tr>";
		echo "<tr><td>{$row['Confidence']}</td></tr>";
	}
	if($extra > 0)
	{
		foreach(range(1,$extra) as $not_used)
		{
			?>
			<tr><td>&nbsp;</td></tr>
			<tr><td>&nbsp;</td></tr>
			<tr><td>&nbsp;</td></tr>
			<tr><td>&nbsp;</td></tr>
			<?php
		}
	}
	?>
	</table>
	</td>
	<?php
}
?>
</tr>
</table>

You should use CSS to style the output, instead of repeating the style markup throughout the document.

[pbismad]

You must do this -

pbismad The yii database extension is PDO. The PDO::FETCH_GROUP fetch mode (added to the ->queryAll(...) method call) will index/pivot the data by the first column in the SELECT list (Time.) This will produce a data structure that you can get a maximum count() of the number of rows per time slot and loop over to produce the output.

[Account]

Should I use the same query to get the number of rows for each time slot or construct another query.

$sql2 = 'SELECT t.Time,s.StudentName, sd.Subject, sd.Topic, sd.Confidence
FROM ascteacherreport as rep
JOIN ascteacherreportdetails t ON rep.ASCTeacherReportId=t.ASCTeacherReportId
JOIN ascteacherreporttimedetails sd ON t.ASCReportDetailsId=sd.ASCReportDetailsId
JOIN student s ON sd.StudentId=s.StudentId
WHERE rep.DateofReport=:date AND rep.userid=:id
order by t.Time, s.StudentName, sd.Subject, sd.Topic, sd.Confidence';

It gives the error as Illegal string offset 'StudentName'

Below is the code

<style>

tr, td{
border : 1px solid black;
}
</style>

<?php

 $sql2 = 'SELECT t.Time,s.StudentName, sd.Subject, sd.Topic, sd.Confidence
	FROM ascteacherreport as rep
	JOIN ascteacherreportdetails t ON rep.ASCTeacherReportId=t.ASCTeacherReportId
	JOIN ascteacherreporttimedetails sd ON t.ASCReportDetailsId=sd.ASCReportDetailsId
	JOIN student s ON sd.StudentId=s.StudentId
	WHERE rep.DateofReport=:date AND rep.userid=:id
	';
$params = [':id' => $model->UserId,':date'=>$model->DateofReport];

 $timedetails = \Yii::$app->db->createCommand($sql2, $params)->queryAll();

$max = max(array_map('count',$timedetails));

?>

<table>
<tr>
<?php
// output the left label column
// 1st row is empty
?>
<td>
<table>
<tr><td>&nbsp;</td></tr>
<?php
foreach(range(1,$max) as $not_used)
{
	?>
	<tr><td>Student Name</td></tr>
	<tr><td>Subject</td></tr>
	<tr><td>Topic</td></tr>
	<tr><td>Confidence</td></tr>
	<?php
}
?>
</table>
</td>
<?php
// output each time column
foreach($timedetails as $time=>$arr)
{
	?>
	<td>
	<table>
	<?php
	echo "<tr><th>$time</th></tr>";
	$extra = $max - count($arr);
	foreach($arr as $row)
	{
		echo "<tr><td><b>".$row['StudentName']."</b></td></tr>";   
		echo "<tr><td>".$row['Subject']."</td></tr>";
		echo "<tr><td>".$row['Topic']."</td></tr>";
		echo "<tr><td>".$row['Confidence']."</td></tr>";
	}
	if($extra > 0)
	{
		foreach(range(1,$extra) as $not_used)
		{
			?>
			<tr><td>&nbsp;</td></tr>
			<tr><td>&nbsp;</td></tr>
			<tr><td>&nbsp;</td></tr>
			<tr><td>&nbsp;</td></tr>
			<?php
		}
			}
	?>
	</table>
	</td>
	<?php
}
?>
</tr>
</table>

The mysql result of the the executed query in $sql2 variable is as below:

[Account]

pbismad

$timedetails = \Yii::$app->db->createCommand($sql2, $params)->queryAll(PDO::FETCH_GROUP);

This change

[Account]

Yes it is working correctly. All the details are correctly displayed in html table row wise. Thanks