html - Php procedure without a nested query, make it useful code -

does know how make procedure 1 query instead of nested query, i'm trying sub categories inside main category in dropdown menu. i've been thiking long, i'm using nested loop cause can't figure out way. !

    <ul role=menu>     <?php          $statement = $database->prepare("select * category");         $statement->execute();         while($category = $statement->fetch(pdo::fetch_assoc))         {     ?>        <li><a href='<?php echo '/'.urlencode($category['name']);?>.html'><?php echo $category['name']; ?></a>           <ul>           <?php              $stmt = $database->prepare("select * area belong_to_category = :category_name");             $stmt->execute(array(':category_name' => $category['name']));             while($sub_category = $stmt->fetch(pdo::fetch_assoc))             {           ?>              <li role="menuitem"><a href='<?php echo '/'.urlencode($sub_category['name']);?>.html'><?php echo $sub_category['name'];?></a>           <?php              }           ?>                   </ul>        </li>     <?php          }     ?>           </ul> 

you use simple join in query statement in conjunction using pdo fetch pdo::fetch_group stated here.

in turn, you'll category keys, corresponding values sub categories.

you haven't posted db schema i'll give example of joining them thru keys.

basic idea:

$sql = '     select category.name category_name, area.name area_name category     join area on area.category_id = category.id '; // simple join  $statement = $database->prepare($sql); $data = $statement->fetchall(pdo::fetch_group | pdo::fetch_assoc); // use `pdo::fetch_group` flag // turns fetched data grouped 1  // ul foreach($data $category => $sub_categories) {     // li     echo $category; // category name         // ol          foreach($sub_categories $s) {             // li             echo $s['area_name']; // area name             // close li         }         // close ol     // li close } // ul close  // fetch necessary depth, idea 

edit. way build array manually. still same query statement without using pdo::fetch_group.

along these lines:

$data = $statement->fetchall(pdo::fetch_assoc); // no fetch group $final_data = array(); foreach($data $values) {     $final_data[$values['category_name']][] = $values['area_name'];     // assign category key, push array container area name }  // going on building desired html markup using manually grouped `$final_data` below