Issue with group and looping in PHP

Question

I have a record grouping occurring through a SELECT * which provides the following result:

 -----------------------------------------------------------------------
 |                      Room_Location - Room_Name                       |
 -----------------------------------------------------------------------
 | Device                         | Technical        | Ergonomic        |
 -----------------------------------------------------------------------
 | Device_Model - Device_Type     | ☐ Pass  ☐ Fail  | ☐ Pass  ☐ Fail  |
 | IP_Address                     |                  |                  |
 -----------------------------------------------------------------------
 | Device_Model - Device_Type     | ☐ Pass  ☐ Fail  | ☐ Pass  ☐ Fail  |
 | IP_Address                     |                  |                  |
 -----------------------------------------------------------------------

 then a new table:

 -----------------------------------------------------------------------
 |                      Room_Location - Room_Name                       |
 -----------------------------------------------------------------------
 | Device                         | Technical        | Ergonomic        |
 -----------------------------------------------------------------------
 | Device_Model - Device_Type     | ☐ Pass  ☐ Fail  | ☐ Pass  ☐ Fail  |
 | IP_Address                     |                  |                  |
 -----------------------------------------------------------------------
 | Device_Model - Device_Type     | ☐ Pass  ☐ Fail  | ☐ Pass  ☐ Fail  |
 | IP_Address                     |                  |                  |
 -----------------------------------------------------------------------

And so on...

I understand that some people are against interweaving code with HTML, but with that point aside, what I am experiencing is: The Room Code and Room Name of the first record is not appearing above the first record, and instead appearing as the header for the second record, and rippling down to the last record.

The last record is actually showing the room code for the above room record above, in it's header.

It's grouping the items correctly, just putting the header details in the wrong area and is almost like it is needing to -1 the recordset but only for the room location and room name.

My connection Code:

 $result = mysqli_query($con,"SELECT * FROM RoomList ORDER BY Room_Code ASC");

Remainder:

  <?php 
    
    /*// Start record fail error checking
    
    if (!$result) {
    printf("Error: %s\n", mysqli_error($con));
    exit();
}
    // Start record fail error checking */

    if (mysqli_num_rows($result) === 0) {?>
<div id="resultserror">
No records could found for the selected view.</div>
          <?php } else { ?>
<?php ;
$data = array();
while ($row = mysqli_fetch_assoc($result)) {
  $itemName = $row["Room_Code"];
  if (!array_key_exists($itemName, $data)) {
    $data[$itemName] = array();
  }
  $data[$itemName][] = $row;
}                       
                        
foreach ($data as $itemName => $rows) {
 
    ?>
          <table id="results" width="100%" cellpadding="3" cellspacing="0">
        <tr><td colspan="4"><?php echo($row['Room_Code']);?> - <?php echo($row['Room_Name']);?></td></tr>
        <tr>
<th width="" align="center">Device</th>
<th width="100" align="center">Technical</th>
<th width="100" align="center">Ergonomic</th>
</tr>

<?php
    foreach ($rows as $row) {
        ?>
    <tr>

<td align='left'>
<strong>Device:</strong><?php echo($row['Device_Model']);?><br>
<strong>Type:</strong> <?php echo($row['Device_Type']);?><br>
<strong>IP Address:</strong> <?php echo($row['IP_Address']);?></td>
<td align='center'>☐ Pass<br>
  ☐ Fail</td>
<td align='center'>☐ Pass<br>
  ☐ Fail</td>
</tr>
<?php
}
?>
<tr>
  <td colspan="7" align="center">- end of room report -</td></tr>
</table>
<?php
}
mysqli_close($con);
                     }?>
<div id="reportgen"><?php echo("Report Generated: " . date("Y-m-d H:i:s")); ?></div>
<div id="reportcount"><?php echo("Record Count: " . mysqli_num_rows($result) . "");?></div>

Answer 1

Your problem is that in this line:

<tr><td colspan="4"><?php echo($row['Room_Code']);?> - <?php echo($row['Room_Name']);?></td></tr>

you are echo'ing from $row, which when you first encounter this code, contains the last row fetched from the table, and on subsequent loops contains the last row of the previous entry. Hence all your headers are shifted by 1 relative to where they should be. You should change that to grab the data from the current row:

<tr><td colspan="4"><?php echo($rows[0]['Room_Code']);?> - <?php echo($rows[0]['Room_Name']);?></td></tr>