SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member spheroid2007's Avatar
    Join Date
    Oct 2006
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PHP OOP Looping thru While within While statement

    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:

    PHP Code:
    $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...

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Member spheroid2007's Avatar
    Join Date
    Oct 2006
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code MySQL:
    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.

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $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"

  5. #5
    SitePoint Member spheroid2007's Avatar
    Join Date
    Oct 2006
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    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:

    PHP Code:
    $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" 

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    um, ya. it was a test. i did it on purpose.

    good job!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •