MySQL join instead of a "nested while loop"

Question

This code works just fine, but i don't like the idea of a while loops within loops.

Is there another way with JOINS that will work more efficiently?

$region_results = mysql_query("SELECT id,region_name FROM regions WHERE page_id='$page_id' ORDER BY position ASC",$con_web) or die (mysql_error());
while($region_rows=mysql_fetch_array($region_results))
{
  $region_id=$region_rows["id"];
  $region_name=$region_rows["region_name"];

  echo "  <li>$region_name\n";
  echo "   <ul>\n";

  $block_results = mysql_query("SELECT id,block_name FROM blocks WHERE region_id='$region_id' ORDER BY position ASC",$con_web) or die (mysql_error());
  while($block_rows=mysql_fetch_array($block_results))
  {
    $block_id=$block_rows["id"];
    $block_name=$block_rows["block_name"];

    echo "    <li>$block_name\n";
    echo "     <ul>\n";

    $object_results = mysql_query("SELECT id,object_name FROM objects WHERE block_id='$block_id' ORDER BY position ASC",$con_web) or die (mysql_error());
    while($object_rows=mysql_fetch_array($object_results))
    {
      $object_id=$object_rows["id"];
      $object_name=$object_rows["object_name"];

      echo "      <li>$object_name</li>\n";
    }

    echo "     </ul>\n";
    echo "    </li>\n";
  }

  echo "   </ul>\n";
  echo "  </li>\n";
}

this code produces:

  <li>Left Content
   <ul> 
    <li>Block 1
     <ul> 
      <li>Object 1</li> 
     </ul> 
    </li> 
    <li>Block 2
     <ul> 
      <li>Object 1</li> 
     </ul> 
    </li> 
    <li>Block 3
     <ul> 
      <li>Object 1</li> 
     </ul> 
    </li> 
   </ul> 
  </li> 
  <li>Right Panel
   <ul> 
    <li>Block 1
     <ul> 
      <li>Object 1</li> 
      <li>Object 2</li> 
     </ul> 
    </li> 
   </ul> 
  </li>

Thank you.

Quassnoi · Accepted Answer · 2011-03-14 18:09:38Z

1

SELECT  b.id AS block_id, b.block_name, o.id, o.object_name
FROM    blocks b
JOIN    objects o
ON      o.block_id = b.id
WHERE   b.region_id = '$region_id'
ORDER BY
        b.position, b.id, o.position

Record the value of block_id and close/open the block tags whenever it changes.

answered Mar 14, 2011 at 18:09

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

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

2 Comments

Lewis Over a year ago

This is where i got stuck! how do echo out the results in a single fetch. so that it looks like my output example?

Quassnoi Over a year ago

@Lewis: if ($prev_block_id != $rows["block_id"]) { close_and_open_block_tags(); }; @prev_block_id = $rows["block_id"];

JohnFx · Accepted Answer · 2011-03-14 18:12:09Z

0

It isn't so much the nested WHILE loops that is going to be an efficiency problem. I think your problem is more that you are making a lot of separate calls to the DB that might be more efficient if they were combined.

Try combining your queries into a single query using joins then loop through the rows detecting when the groupings change.

answered Mar 14, 2011 at 18:12

JohnFx

35k19 gold badges108 silver badges169 bronze badges

Comments

bluefoot · Accepted Answer · 2011-03-14 18:20:35Z

0

what is wrong with whiles within whiles? unless they are not guarantee to end, and if you could avoid them by querying just one time, I don't see any problem.

Maybe you should use a more high level API to do this job. That would be better for your code.

edited Mar 14, 2011 at 18:20

answered Mar 14, 2011 at 18:11

bluefoot

10.6k12 gold badges46 silver badges57 bronze badges

2 Comments

Ronnis Over a year ago

Performing SQL queries in a loop is a performance anti-pattern. How many database queries would it take if there are 20 regions, 10 blocks within each region and 10 objects within each block?

bluefoot Over a year ago

for sure (+1). That's why I would use a more high level API and concentrate in my business logic.

Collectives™ on Stack Overflow

MySQL join instead of a "nested while loop"

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related