Menu display in php pages

I am creating CMC pages from the admin side. I have a 3 level menu in the front end. My database table structure for CMS pages has the fields name, parent_id, description, status.

Now I have to display all pages, subpages and subpages of subpages in the menu. How to write this query?

<!-- BEGIN Menu -->
     <ul id="nav">
 <li><a href="consumers.php">Consumers</a>
   <ul><li><a href="personal_finance.php">Personal Finance </a>
 <ul><li><a href="personal_financial_makeover.php">Personal Financial Makeover</a></li>
       <li><a href="personal_finance_consultancy.php">Personal Finance Consultancy</a></li>
      </ul>
     </li>
     <li><a href="investings.php">Investings</a>
      <ul>
  <li><a href="securities_analysis.php">Securities Analysis</a></li>
      <li><a href="portfolio_investment.php">Bernie Madoff Shield (Portfolio and Investment Analysis)</a></li>
     </ul>
     </li>
    </ul>
          </li>
         <li><a href="entrepreneurs.php">Entrepreneurs</a>
            <ul>
     <li><a href="market_analysis.php">Market Analysis and Competitive Report </a></li>
              <li><a href="business_plans_feasibility.php">Business Plans &amp; Feasibility Studies </a>
             <ul>
             <li><a href="business_plans.php">Business Plans</a>
                    <ul>
                       <li><a href="level1.php">Level 1</a></li>
                       <li><a href="level2.php">Level 2</a></li>
                       <li><a href="level3.php">Level 3</a></li>
                     </ul>

                   </li>
       </ul>

      </li>
                   </ul>

  <!-- END Menu -->

---

as you said i tried. i have home page. it doesnot have childs. still its getting empty dropdown menus. and next displaying firstlevel menus correctly. and also displaying secondlevel menus. actually mouse over on firstlevel menus dropdown appear. i am getting dropdown. upto now fine. but dispalying menu names openly. see my attachment please. i was attached 2images. in first images directly displaying like this. in second image on mouseover on firstlevel displaying dropdown.

<? 

$query = "SELECT
    t1.auto_id AS l1_id, t1.name AS l1_name
    ,t2.auto_id AS l2_id, t2.name AS l2_name
    ,t3.auto_id AS l3_id, t3.name AS l3_name
FROM static_pages AS t1
LEFT JOIN static_pages AS t2 ON t2.parent_id = t1.auto_id
LEFT JOIN static_pages AS t3 ON t3.parent_id = t2.auto_id";

$result = mysql_query( $query );

$results = array() ;
while($row=mysql_fetch_assoc($result)){
    $results[] = $row;
}

// Building the menu. Quick and dirty.
$menu = array() ;
foreach( $results as $result ) {
    $menu_level_3 = array( 'auto_id' => $result['l3_id'], 'name' => $result['l3_name'], 'children' => array() ) ;
    $menu_level_2 = array( 'auto_id' => $result['l2_id'], 'name' => $result['l2_name'], 'children' => array( $menu_level_3['auto_id'] => $menu_level_3 ) ) ;
    $menu_level_1 = array( 'auto_id' => $result['l1_id'], 'name' => $result['l1_name'], 'children' => array( $menu_level_2['auto_id'] => $menu_level_2 ) ) ;

    if( !isset( $menu[$menu_level_1['auto_id']] ) ) {
        $menu[$menu_level_1['auto_id']] = $menu_level_1 ;
    } elseif( !isset( $menu[$menu_level_1['auto_id']]['children'][$menu_level_2['auto_id']] ) ) {
        $menu[$menu_level_1['auto_id']]['children'][$menu_level_2['auto_id']] = $menu_level_2 ;
    } else {
        $menu[$menu_level_1['auto_id']]['children'][$menu_level_2['auto_id']]['children'][$menu_level_3['auto_id']] = $menu_level_3;
    }
}
?>
<ul id="nav">
<? foreach($menu as $item0) : ?>
    <li>
    <a href="?page=<?=$item0['auto_id']?>"><?=$item0['name']?></a>
    <? if(!empty($item0['children'])){?>
    <ul>
    <? foreach($item0['children'] as $item1) : ?>
        <li>
        <a href="?page=<?=$item1['auto_id']?>"><?=$item1['name']?></a>
        <? if(!empty($item1['children'])){?>
        <ul>
        <? foreach($item1['children'] as $item2) : ?>
            <li><a href="?page=<?=$item2['auto_id']?>"><?=$item2['name']?></a></li>
        <? 开发者_Python百科endforeach; ?>
        </ul>
        <? }?>
        </li>
    <? endforeach; ?>
    </ul>
    <? }?>
    </li>
<? endforeach;exit;?>
</ul>

---

I suggest that you read Managing Hierarchical Data in MySQL.

The query would be:

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3
FROM pages AS t1
LEFT JOIN pages AS t2 ON t2.parent = t1.pages_id
LEFT JOIN pages AS t3 ON t3.parent = t2.pages_id;

UPDATE

$query = '
SELECT
    t1.pages_id AS l1_id, t1.name AS l1_name
    ,t2.pages_id AS l2_id, t2.name AS l2_name
    ,t3.pages_id AS l3_id, t3.name AS l3_name
FROM pages AS t1
LEFT JOIN pages AS t2 ON t2.parent = t1.pages_id
LEFT JOIN pages AS t3 ON t3.parent = t2.pages_id
';

$result = mysql_query( $query );

$results = array() ;
while($row=mysql_fetch_assoc($result)){
    $results[] = $row;
}

// Building the menu. Quick and dirty.
$menu = array() ;
foreach( $results as $result ) {
    $menu_level_3 = array( 'id' => $result['l3_id'], 'name' => $result['l3_name'], 'children' => array() ) ;
    $menu_level_2 = array( 'id' => $result['l2_id'], 'name' => $result['l2_name'], 'children' => array( $menu_level_3['id'] => $menu_level_3 ) ) ;
    $menu_level_1 = array( 'id' => $result['l1_id'], 'name' => $result['l1_name'], 'children' => array( $menu_level_2['id'] => $menu_level_2 ) ) ;

    if( !isset( $menu[$menu_level_1['id']] ) ) {
        $menu[$menu_level_1['id']] = $menu_level_1 ;
    } elseif( !isset( $menu[$menu_level_1['id']]['children'][$menu_level_2['id']] ) ) {
        $menu[$menu_level_1['id']]['children'][$menu_level_2['id']] = $menu_level_2 ;
    } else {
        $menu[$menu_level_1['id']]['children'][$menu_level_2['id']]['children'][$menu_level_3['id']] = $menu_level_3;
    }
}
?>
<ul>
<? foreach($menu as $item0) : ?>
    <li>
    <a href="?page=<?=$item0['id']?>"><?=$item0['name']?></a>
    <ul>
    <? foreach($item0['children'] as $item1) : ?>
        <li>
        <a href="?page=<?=$item1['id']?>"><?=$item1['name']?></a>
        <ul>
        <? foreach($item1['children'] as $item2) : ?>
            <li><a href="?page=<?=$item2['id']?>"><?=$item2['name']?></a></li>
        <? endforeach; ?>
        </ul>
        </li>
    <? endforeach; ?>
    </ul>
    </li>
<? endforeach; ?>