ytse_jam ytse_jam - 1 year ago 56
MySQL Question

php mysql many to many relationship query result display

I'm just wondering if you can help me organize the query result from many to many relationship, below are other details.

enter image description here

here is my query:

SELECT rt.room_title as rm_name, amn.amnty_title
FROM amenities_tbl AS amn
LEFT JOIN room_am_link AS ral ON amn.amnty_id = ral.am_id
LEFT JOIN rooms_tbl AS rt ON ral.rm_id = rt.room_id;

and here is my foreach loop on the query result (codeigniter):

<?php foreach($result as $row){?>
<td><?php echo $row['rm_name']; ?></td>
<td><?php echo $row['am_title']; ?></td>
<?php } ?>

the display result is show below,

enter image description here

but what i want the display to be is like this image below, the room title is not repeated:

enter image description here

i'm sorry if my questions is very long, i just want it to be clear as much as possible.

thank you and hope you can help me guys.

Answer Source

simple way of doing this is to have a variable, that will keep value of the item from the first column, then in loop, you are displaying it only if value will change, eg.:

   $first_col = NULL;

   foreach($result as $row){ ?>
             if ($first_col != $row['rm_name']) {
               echo $row['rm_name'];
               $first_col = $row['rm_name'];
             } else {
               echo "&nbsp;";
             }; ?>
      <td><?php echo $row['am_title']; ?></td>
<?php } ?>

of course, your data should be sorted by this first column - you need to add ORDER BY to your SQL.