PHP OOP Looping thru While within While statement

[spheroid2007]

Using the PHP Anthology MySQL.php class, I'm wondering can you loop through a row fetch statement within another one? I know I could use a LEFT JOIN probably, but i'll end up with duplicate data in the array before displaying it. Something like this...My first $sql should grab a product number a person is linked to...basically an array of just product id's:

$sql = "
SELECT product_id
FROM table1
WHERE ....
";

$result = $db->query($sql);

echo "<ul>";

while ($row = $result->fetch())
{
   echo $row['product_id'];

   $sql2 =
   "SELECT product_name, product_size, product_color
    FROM ....";

   $result2 = $db->query($sql2);

   while ($row2 = $result2->fetch())
   {
      echo "<li>" . $row['product_name'] . "(Size:" . $row['product_size'] . " | Color: " .  $row['product_color'] . ")<br>";
   }
} 

It doesn't give me attributes for the 2nd, 3rd etc products...like it's stopping at the first product. Should I be able to do this? I'm new to OOP...

[longneck]

use a LEFT OUTER JOIN. that is what they are designed for. adding a bit of code to deal with the duplicates is WAY more efficient than making multiple calls to the database inside a loop.

[spheroid2007]

Thanks! Without giving my actual tables away...I ended up using LEFT JOIN and LEFT INNER JOIN...works great. Now, each product has several sub products attached, and attributes along with it. My product_id is repeated in the results...similar to:

product_id	product_name	sub_product_name	subproduct_size
1000		Product 1	Nifty Accessory		NULL
1000		Product 1	Another Accessory	XL
1000		Product 1	A third accessory	NULL
1001		Product 2	More accessories	XL
1001		Product 2	Still more stuff	L

How would I take this result set and now, say, make a UL list out of it, without duplicating the product_id...something like:

Product 1 - Related items found are:
* Nifty Accessory
* Another Accessory
* A third Accessory

Product 2 - Related items found are:
* More accessories
* Still more stuff

I'm trying to use the OOP example to pull this data the way I want it displayed...the OOP way is much easier and I'm trying to learn as fast as possible.

[longneck]

$prev_product_id = '';
$in_list = false;

while ($row = $result->fetch()) {
  if ($prev_product_id != $row['product_id']) {

    if ($in_list) {
      echo "</ul>\n";
      $in_list = false;
    }

    echo '<h1>', $row['product_name'], " - Related items are:</h1>\n";
    echo "<ul>\n";
    $in_list = true;
  }
  
  echo '<li>', $row['sub_product_name'], "</li>\n";
}

echo "<ul>\n"; 

[spheroid2007]

Longneck: Thanks! This put me on the right track. It was outputting the product name multiple times like this:

Product 1 - Related items found are:
* Nifty Accessory

Product 1 - Related items found are:
* Another Accessory

Product 1 - Related items found are:
* A third Accessory

I had to add a line to tell it to set the current product id. Here's the final result:

$prev_product_id = '';
$in_list = false;

while ($row = $result->fetch()) {
  if ($prev_product_id != $row['product_id']) {

    if ($in_list) {
      echo "</ul>\n";
      $in_list = false;
    }

    $prev_product_id = $row['product_id'];
    echo '<h1>', $row['product_name'], " - Related items are:</h1>\n";
    echo "<ul>\n";
    $in_list = true;
  }
  
  echo '<li>', $row['sub_product_name'], "</li>\n";
}

echo "<ul>\n" 

[longneck]

um, ya. it was a test. i did it on purpose.

good job!