如何在MySQL中连接多个表，然后使用PHP将它们显示为每个新父ID的一组表

Question

Firstly, here is my ERD to provide the necessary background:

https://i.imgur.com/idVR5ev.png

Secondly, here is the intended final result I wish my code to achieve:

https://i.imgur.com/4h7l49p.png

To confirm: I want the ability to create any number of new ProductionOrders (parent) which can have Zero, One or Many Batch Orders (Child) which the Batch Orders themselves are made up of Zero, One or Many Customer Orders (Child of Child).

My main issue here is knowing how to duplicate the 3 tables (ProductionOrder, BatchOrder & CustomerOrder) for each ProductionOrderID as seen in my second screenshot.

---

My progress so far:

connect-db.php

<?php
$server = "";
$user = "";
$pass = "";
$db = "";
$mysqli = new mysqli($server, $user, $pass, $db);
mysqli_report(MYSQLI_REPORT_ERROR);
?>

This is where my knowledge stops and I am unable to find an existing example that I can work into my requirements but what I've tried so far:

Index.php

<?php
include('connect-db.php');
if ($result = $mysqli->query(
    "SELECT * FROM ProductionOrder p 
    INNER JOIN ProductionOrderStatus s 
    ON p.ProductionOrderID = s.ProductionOrderStatusID 
    INNER JOIN NotGood n 
    ON p.ProductionOrderID = n.NGID 
    INNER JOIN BatchOrder b 
    ON p.ProductionOrderID = b.BatchID"
))
{
    if ($result->num_rows > 0)
    {
        echo "<table class='table table-striped'>";
        echo "<tr> <th>PO ID</th> <th>PO #</th> <th>Order Quantity</th> <th>Balance Left</th> <th>Production Date</th> <th>PO Status</th> </tr>";
        while ($row = $result->fetch_object())
        {
            echo "<tr>";
            echo "<td>" . $row->ProductionOrderID . "</td>";
            echo "<td>" . $row->PONum . "</td>";
            echo "<td>" . $row->OrderQTY . "</td>";
            echo "<td>" . $row->BalLeftNum . "</td>";
            echo "<td>" . $row->ProductionDate . "</td>";
            echo "<td>" . $row->ProductionOrderStatus . "</td>";
            echo "</tr>";
            echo "</table>";
            //Display Batch Orders for contained Production Order
            echo "<table class='table table-striped'>";
            echo "<tr> <th>Batch ID</th> <th>Batch Quantity</th> <th>Available Date</th> <th>Balance Left</th> <th>Production Order</th> </tr>";
            echo "<tr>";
            echo "<td>" . $row->BatchID . "</td>";
            echo "<td>" . $row->BatchQTY . "</td>";
            echo "<td>" . $row->AvailDate . "</td>";
            echo "<td>" . $row->RemainBal . "</td>";
            echo "<td>" . $row->ProductionOrderID . "</td>";
            echo "</tr>";
            echo "</table>";
            //Display Customer Orders
            echo "<table class='table table-striped'>";
            echo "<tr> <th>Customer Order ID</th> <th>Invoice Quantity</th> <th>Invoice Number</th> <th>Shipment Date</th> <th>Batch Order</th> </tr>";
            echo "<tr>";
            echo "<td>" . $row->COID . "</td>";
            echo "<td>" . $row->InvoiceQTY . "</td>";
            echo "<td>" . $row->InvoiceNum . "</td>";
            echo "<td>" . $row->ShipDate . "</td>";
            echo "<td>" . $row->BatchID . "</td>";
            echo "</tr>";
            echo "</table>";
        }
    }
    else
    {
        echo "No results to display!";
    }
}
else
{
    echo "Error: " . $mysqli->error;
}
$mysqli->close();
?>

I am fully aware this code is only capable of showing the results once but I wanted to show something vs nothing as I am hoping to understand the requirements and not just have someone write the answer no questions asked...

I do not know how to properly join the tables I need and group them. I have tried GROUP BY, INNER JOIN and tried to create a tree using PHP but have been unable to get the desired results via any method.

Answer 1

here what you can do is first retrieve all the required data with respect to production order id for example like this

$result = [
   0:{
     "poid":1,
     "po":2,
     "order_quantity":3,
     "batch_id": 2,
     "customer_id": 4,
     and all the required data for production order one

   },
   1:{
     "poid":1,
     "po":2,
     "order_quantity":3,
     "batch_id": 2,
     "customer_id": 4,
     and all the required data for production order two

   }
]

this array structure might not be right but something like this which can be used in foreach loop. now you can loop array in foreach and print the table (i.e, three table). for example

 foreach($result as $r){ 
//PO table
<table>
  <thead>
    <tr>
     <td>PO ID</td>
     <td>PO#</td>
     <td>Balance left</td>
     //etc.
    <tr>
 </thead>
 <tbody>
   <tr>
     <td><?php echo $r->poid; ?>
     <td><?php echo $r->po; ?>
     <td><?php echo $r->po_quantity; ?>
     //etc.
   </tr>
 </tbody>
</table>

//Batch table
<table>
   <thead>
      <tr>
       <td>Batch Id</td>
       <td>Batch Quantity</td>
       <td>Abailable date</td>
       //etc. 
      <tr>
   </thead>
   <tbody>
      <tr>
       <td><?php echo $r->batchid; ?>
       <td><?php echo $r->batchquantity; ?>
       <td><?php echo $r->date; ?>
       //etc.
      </tr>
   </tbody>
</table>
//and same for customer table
 }

hope this gives you some idea