CODEWITHSUNDEEP
phpmysqlloopsjoin
Lewis
Lewis

Reputation: 33

MySQL join instead of a "nested while loop"

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.

Upvotes: 2

Views: 1097

Answers (3)

bluefoot
bluefoot

Reputation: 10580

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.

Upvotes: 0

JohnFx
JohnFx

Reputation: 34917

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.

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425823

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.

Upvotes: 1

Related Questions