Nested mysql while loop not returning expected results

Question

I have 2 tables, and I'm trying to use a nested while loop to list all possibilities. Unfortunately, I'm only getting a small subset of results. Any help is much appreciated.

Products Table

Product_ID | Product_Name | Product_Desc
0          | Box          | Big Box     
1          | Toy          | Nice Toy

City Table

City_ID    | City_Name 
0          | Cincinnati    
1          | Detroit
2          | San Francisco
3          | San Diego
4          | New York

Expected desired results:

0 Box Big Box 0 Cincinnati
0 Box Big Box 1 Detroit
0 Box Big Box 2 San Francisco
0 Box Big Box 3 San Deigo
0 Box Big Box 4 New York
1 Toy Nice Toy 0 Cincinnati
1 Toy Nice Toy 1 Detroit
1 Toy Nice Toy 2 San Francisco
1 Toy Nice Toy 3 San Diego
1 Toy Nice Toy 4 New York

My Incorrect Results:

0 Box Big Box 0 Cincinnati
1 Toy Nice Toy 0 Cincinnati

Here is my code:

query = "SELECT * FROM Products";
$result = mysqli_query($con, $query);

$query2 = "SELECT * FROM Cities";
$result2 = mysqli_query($con, $query2);


while($row = mysqli_fetch_assoc($result))
    {
         $product_id = $row['Product_ID'];
         $product_name = $row['Product_Name'];
         $product_desc = $row['Product_Desc'];

        while($row = mysqli_fetch_assoc($result2))
        {
        $city_id = $row['City_ID'];
        $city_name = $row['City_Name'];
        }

    echo "$product_id $product_name $product_desc $city_id $city_name\n";

}

Unfortunately, the above is not working and I'm only getting this result. Should I be using a foreach product instead?

Jeffry Evan · Accepted Answer · 2018-05-04 01:00:13Z

1

actually you can get the same result with single query without using nested loop

$query = "SELECT p.*,c.* FROM Products p,City c";

answered May 4, 2018 at 1:00

Jeffry Evan

3271 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wyatt Jackson Over a year ago

Very quick clean solution. Thank you.

Collectives™ on Stack Overflow

Nested mysql while loop not returning expected results

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related