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

Question

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

But The results am looking for should display as follows

How can i achieve this?

This is a classic control break implementation. Your data needs to be properly sorted to begin with. And then, in your loop over the data, you check if value (of whatever you call that first column) for the current row, it still the same, as it was for the last one. Depending on that, you decide which output needs to be created for the current row. — C3roe
– C3roe, Commented Mar 15, 2021 at 10:56
I can't see how the desired result set relates to the other one — Strawberry
– Strawberry, Commented Mar 15, 2021 at 11:24

Gowire · Accepted Answer · 2021-03-15 11:00:16Z

1

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)

answered Mar 15, 2021 at 11:00

Gowire

1,0707 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Strawberry Over a year ago

You can ORDER BY in the query or sort the resulting array. Unless LIMITing the result, it makes no difference.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related