php - fetching all sub-list items under each row using foreach loop inside while loop

Question

I have 2 tables in my product database:

1. product_list(id, Product_ID, Product_Name, Supplier),
2. product_option (id, Product_ID, Color, Size). both 'id's are primary keys with auto_increment.

I want to print all Color and Size values under each Product_Name (without repetition) that is from product_list table. I've been trying to figure out how to properly use foreach loop within while loop but now I'm out of related search result.

How my tables look:

product_list table:

|id  | Product_ID | Product_Name | Supplier |
| -- | ---------- | ------------ | -------- |
| 1  |A1          | product1     | company1 |
| 2  |A2          | product2     | company2 |
| 3  |A3          | product3     | company3 |
| 4  |A4          | product4     | company4 |

product_option table:

|id  |Product_ID | Color | Size |
| -- | --------- | ----- | ---- | 
| 1  |A1         | red   | S    |
| 2  |A1         | red   | M    |
| 3  |A1         | black | S    |
| 4  |A1         | black | M    |
...

My expected output is:

| Product_ID | Product_Name | Supplier    |
|:----------:|:------------:|:-----------:|
|  A1        | Product1     | companyname |
|            | red S        |             |
|            | red M        |             |
|            | black S      |             |
|            | black M      |             |
|  A2        | Product2     | companyname |
|            | Large        |             |

Color and Size from product_option table with the same Product_ID will display under Product_Name row and Product_Name from product_list will only display once (instead of 4 times in the case of A1).

These are my code so far: (didn't write any table or styling for clean view)

include_once 'includes/dbh.inc.php';

$sql = "
SELECT
    pl.Product_ID pid,
    po.Product_ID poid,
    pl.Product_Name,
    po.Color color,
    po.Size size,
    pl.Supplier
FROM
    product_list pl
LEFT JOIN
    product_option po ON pl.Product_ID = po.Product_ID
ORDER BY
    pl.Product_ID;";

$result = mysqli_query($conn, $sql) or die(mysqli_error());

if ($result -> num_rows > 0){

    while ($row = $result -> fetch_assoc()) {
        
        echo $row['pid'] . "&nbsp;" . $row['Product_Name'] . "&nbsp;" . $row['Supplier'] . "<br><br>";

        if (!empty($row['color'] || $row['size'])) {
            foreach ($row as $data) {
                
                echo $data['color'] . ' /' . $data['size'] . '<br><br>';
            }
        }
    }
    
}

Connection file: I use Xampp - phpmyadmin.

$dbServername = "localhost";
$dbUsername = "root";
$dbPassword = "";
$dbName = "product";

// Create Connection
$conn = new mysqli ($dbServername, $dbUsername, $dbPassword, $dbName);

// Check Connection
if ($conn -> connect_error) {
    die("Connection Failed: " . $conn -> connect_error);
}

I'm ashamed to admit that the second 'if' and the foreach doesn't seem to work, and I don't know where to include the Product_ID match condition.. So far the output of this code is just 'A1 product1 company1', only the first result of the while loop.

Answer 1

From comment:

If it's ok for you to change how the data is being showed in the field, I suggest to make it horizontal with a query like this:

SELECT
    pl.Product_ID pid,
    po.Product_ID poid,
    pl.Product_Name,
    group_concat(concat(color,' ',size) separator ', ') AS Product_Name,
    pl.supplier
FROM
    product_list pl
LEFT JOIN
    product_option po ON pl.Product_ID = po.Product_ID
GROUP BY pl.Product_ID, po.Product_ID,pl.Product_Name, pl.supplier
ORDER BY
    pl.Product_ID;

Returns value like following:

+-----+------+---------------+---------------------------------+----------+
| pid | poid |  Product_Name | Product_Name                    | supplier |
+-----+------+---------------+---------------------------------+----------+
|  A1 | A1   |  product1     | black M, black S, red M, red S  | company1 |
.....

A fiddle of the tests