Erick Mongi Erick Mongi - 4 days ago 4
MySQL Question

I want to display subject for specific class i.e A or B but current I display only A?

This is my database table(TimeTable)

This is query I try to create in order to display teacher timetable on the webpage

$sqlquery = "
SELECT day,tid,progid,class,
MAX( CASE WHEN time = '08:00-08:40' THEN subject END) '08:00-08:40',
MAX( CASE WHEN time = '08:40-09:20' THEN subject END) '08:40-09:20',
MAX( CASE WHEN time = '09:20-10:00' THEN subject END) '09:20-10:00',
MAX( CASE WHEN time = '10:00-10:15' THEN subject END) '10:00-10:15',
MAX( CASE WHEN time = '10:15-10:55' THEN subject END) '10:15-10:55',
MAX( CASE WHEN time = '10:55-11:35' THEN subject END) '10:55-11:35',
MAX( CASE WHEN time = '11:35-12:15' THEN subject END) '11:35-12:15',
MAX( CASE WHEN time = '12:15-1:15' THEN subject END) '12:15-1:15',
MAX( CASE WHEN time = '01:15-01:55' THEN subject END) '01:15-01:55',
MAX( CASE WHEN time = '01:55-02:35' THEN subject END) '01:55-02:35'

FROM timetable GROUP BY tid ORDER BY 1";

//This is a loop
while($rows = $res->fetch_assoc()){

print "<tr>";
print "<td>".$rows["day"]."</td>";
print "<td>".$rows["08:00-08:40"]."</td><td>".$rows["class"]."</td>";
print "<td>".$rows["08:40-09:20"]."</td><td>".$rows["class"]."</td>";
print "<td>".$rows["09:20-10:00"]."</td><td>".$rows["class"]."</td>";
print "<td>".$rows["10:00-10:15"]."</td><td>".$rows["class"]."</td>";
print "<td>".$rows["10:15-10:55"]."</td><td>".$rows["class"]."</td>";
print "<td>".$rows["10:55-11:35"]."</td><td>".$rows["class"]."</td>";
print "<td>".$rows["11:35-12:15"]."</td><td>".$rows["class"]."</td>";
print "<td>".$rows["12:15-1:15"]."</td><td>".$rows["class"]."</td>";
print "<td>".$rows["01:15-01:55"]."</td><td>".$rows["class"]."</td>";
print "<td>".$rows["01:55-02:35"]."</td><td>".$rows["class"]."</td>";
print "</tr>";

}


I need help to solve this problem guys.

enter image description here

Answer

You trouble is in the GROUP BY. MySql have no strict restrictions (by default) that each column should use aggregation function, so the result is the first row, instead of complicating SQL use PHP, your result seems not to be huge, so another loop will not affect performance:

$timesVariants = array("08:00-08:40", "08:40-09:20", "09:20-10:00", "10:00-10:15", "10:15-10:55", "10:55-11:35", "11:35-12:15", "12:15-1:15", "01:15-01:55","01:55-02:35");

$sqlquery = "SELECT * FROM timetable";

$classes = array();
while($row = $res->fetch_assoc()) {
   $classes[$row['day']][$row['tid']][$row['time']] = array('subject'=> $row['subject'], 'class' => $row['class'], 'progid' => $row['progid']);
}

//This is a loop
foreach($classes as $day => $daySchedule) {
 foreach($daySchedule as $teacher) {

    print '<tr>';
    print "<td>$day</td>";
    foreach($timesVariants as $time) {
      if (empty($teacher[$time]))
         print "<td>None</td><td>None</td>";
      else
         print '<td>' . $teacher[$time]['subject'] . '</td><td>' . $teacher[$time]['class'] . '</td>';
    } 
    print '</tr>';
 }
}
Comments