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

Question

I have 2 tables in my product database:

product_list(id, Product_ID, Product_Name, Supplier),
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.

You have an error. mysqli_error() needs one argument. Please consider switching error mode on instead. How to get the error message in MySQLi? — Dharman
– Dharman ♦, Commented Jan 20, 2021 at 0:16
What if the result shown horizontally instead? Like | A1 | Product1, red S, red M, black S, black M | companyname | — FanoFN
– FanoFN, Commented Jan 20, 2021 at 0:50
@fadlikidd I would like to later add summed amount for each option items and that's why I need them in rows. Although I'd be glad to learn how to put the looped options in one column because all I can do is make the entire loop of that row which prints option items in each row. — choyoung
– choyoung, Commented Jan 20, 2021 at 7:51
Look at this fiddle and see if you can get any idea from it dbfiddle.uk/… — FanoFN
– FanoFN, Commented Jan 20, 2021 at 8:02
@fadlikidd OH! I got it to work with your example. So I didn't need another loop to just loop within, I just needed to use group_concat! I will edit my post with the result. How do I choose it as an answer though? — choyoung
– choyoung, Commented Jan 20, 2021 at 9:10

FanoFN · Accepted Answer · 2021-01-21 09:25:53Z

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

OTBADMIN · Accepted Answer · 2021-01-19 23:41:02Z

-1

if ($result -> num_rows > 0) mysqli_num_rows() This is a function so it is being ignored, it will always be "Zero" 0 so you will only get the indexed result which begins with '0'.. e.g [ 0,1,3]

answered Jan 19, 2021 at 23:41

OTBADMIN

342 bronze badges

1 Comment

Dharman Over a year ago

I have absolutely no idea what you were trying to say. mysqli_num_rows() is a function but $result->num_rows is a property. Either one of them can return a number greater than 0.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related