phpgrouping

How can i group data from mysql database into an html table using php


I have two table s that i am joining together as follows

    <?php 
    $userqry="
    SELECT * 
      FROM permission_category c
      JOIN permission_group g
        ON c.perm_group_id = g.id 
    ";
    $stmt = $conn2->prepare($userqry);
    $stmt->execute();
    while($row= $stmt->fetch()){
     } ?>
   <tr>
   <td><?php echo $row['name'];?></td>
   <td><?php echo $row['perm_group_name'];?>
   </td>
   <td>
   </tr>
<?php } ?>

Results are as follows enter image description here

But The results am looking for should display as follows

enter image description here

How can i achieve this?


Solution

  • This code "remembers" the previous name in your loop. If the name isn't changed it won't be displayed:

    <?php 
    $userqry = "SELECT * FROM `permission_category` 
                INNER JOIN `permission_group` ON 
                `permission_category`.`perm_group_id`=`permission_group`.`id`";
    $stmt = $conn2->prepare($userqry);
    $stmt->execute();
    
    $current_name="";
    while ($row= $stmt->fetch()) {
      if ($row['name'] != $current_name) {
        $name = $row['name'];
        $current_name = $name;
      } else {
        $name = "";
      }
    ?>
    <tr>
      <td><?php echo $name;?></td>
      <td><?php echo $row['perm_group_name'];?>
      </td>
      <td>
    </tr>
    <?php } ?>
    

    Note: You should add ORDER BY in your query in order to sort your result in a proper way (maybe it's already sorted)