Displaying a separate result from SQL in while-CodePudding

Hello i'm a beginner in these matters.

Yesterday i got acquainted with something like a group_concat() function of sql. I've got tables called "subjects" and "grades". In subjects there is stored "id, name, subject_type" and in grades is "id, grade, subject_type".

So if subjects got subject_type = 1 it will shows all of grades for that type.

I've created code like this:

<?php 
$sqltest1 = "SELECT s.id AS id,  s.name AS name, group_concat(g.grade SEPARATOR ',') as grades, s.teacher_1 as teacher_1
FROM subjects s 
INNER JOIN grades g ON (g.subject_type = s.subject_type)
GROUP BY s.id"; 
?>
<table class="table table-bordered">
      <thead>
        <tr>
          <th class="col-1">#</th>
          <th class="col-3 text-center">Subject</th>
          <th class="col-6 text-center">Grade</th>
          <th class="col-1 text-center">Options</th>
        </tr>
      </thead>
      <tbody>
    <!-- ###################################################################### -->
      <?php
      $result = $conn->query($sqltest1);
      if ($result->num_rows > 0) {
        $i = 1;

        // output data of each row
        while($row = $result->fetch_assoc()) {
          //$id = $row['id'];
          echo "<tr>";
          echo "<td scope='row'>". $i ."</td>";
          echo "<td>". $row['name'] ."</td>";
          echo "<td><span class='bg-primary'>". $row["grades"] ."</span></td>";
          echo "<td class='text-center'>". $row['teacher_1']."</td>";
          echo "</tr>";
          $i  ;
      }
 
    
    } else {
    echo "";
    }
    
    ?>
    
    <!-- ###################################################################### -->
    </tbody>
    </table>

So my problem is that i can't retrive separate grades in one column. I would like to get all grades of a given type but to shows as separate grade.

CodePudding user response：

The while should be like this (check the apart inside the while) You get an array by splitting the value by every char you use to separate it: a comma. Then you foreach all the elements and write a span inside.

    // output data of each row
    while($row = $result->fetch_assoc()) {
      //$id = $row['id'];
      echo "<tr>";
      echo "<td scope='row'>". $i ."</td>";
      echo "<td>". $row['name'] ."</td>"; 

      echo "<td>";
      $arrayGrades = explode(",",  $row["grades"]);
      foreach ($arrayGrades as $grade) {
      echo "<span class='bg-primary'>". $grade ."</span>";
      }
      echo "</td>";
      
      echo "<td class='text-center'>". $row['teacher_1']."</td>";
      echo "</tr>";
      $i  ;
  }