TalkPHP - Menu with only one query?

TalkPHP (http://www.talkphp.com/forums.php)

- General (http://www.talkphp.com/general/)

- - Menu with only one query? (http://www.talkphp.com/general/2305-menu-only-one-query.html)

Menu with only one query?

Hi, i'm dealing with the following issue.
I'm willing to output a menu of two levels.

If an item contains a parentId it is a subitem, if not; it is a 'main' item.

I want to output this in a nested list. How to do this with only one query?

Okay, let's clearify with a working example (the ugly way with more than one queries):

PHP Code:


		
			
            <div id="nav">

<?php

$szQuery = "SELECT id, name FROM menu WHERE parentId = 0 ORDER BY rank ASC";

$pResult = mysql_query($szQuery) or die(mysql_error());



if (mysql_numrows($pResult))

{ ?>

                <ul>

<?php

    while($aRow = mysql_fetch_array($pResult))

    { ?>

                    <li<?php echo ($aRow['id'] == $iMenu ? ' class="active"' : ''); ?>>

                        <a href="index.php?menuid=<?php echo $aRow['id']; ?>"><?php echo $aRow['name']; ?></a>

<?php    $szSubQuery = "SELECT id, name FROM menu WHERE parentId = {$aRow['id']} ORDER BY rank ASC";

        $pSubResult = mysql_query($szSubQuery) or die(mysql_error());

        

        if (mysql_numrows($pSubResult))

        { ?>

                    

                        <ul>

<?php        while($aSubRow = mysql_fetch_array($pSubResult))

            { ?>

                            <li<?php echo ($aSubRow['id'] == $iMenu ? ' class="active"' : ''); ?>><a href="index.php?menuid=<?php echo $aSubRow['id']; ?>"><?php echo $aSubRow['name']; ?></a></li>

<?php        } ?>

                        </ul>

                    </li>

<?php    }

        else

        { ?>

                    </li>

<?php    }

    } ?>

                    <li<?php echo ($szTitle == 'Contact' ? ' class="active"' : ''); ?>>

                        <a href="contact.php">Contact</a>

                    </li>

                </ul>

<?php

    } ?>

            </div>

How would I do this with only one query? :)

Basicly you select all the items from your menu table, and then let php work it out.
Hope it helpes.

PHP Code:


		
			
<?php



/// Select from the table

/// Delimiters if parent = 0 = main. if parent != 0 sub child of 



while ( $r = mysql_fetch_array( $result) ) {

    if ( $r['parent'] == 0) {

            $parent[$r['id']] = $r;

    } else {

        $parent[$r['parent']]['subpage'][] = $r;

    }

}

// So you will have thin in your array

/*



 [1] => array( 'id' => '1',

               'name' => 'Main',

               'subpage' => array ( [0] => array( 'id' => '3',

                                                  'name' => 'Main Sub Page 1'

                                                 ),

                                    [1] => array( 'id' => 12,

                                                  'name' => 'Main Sub Page 2'

                                                 )

                                   )

             ),

   [2] .....

*/



foreach ( $parent as $id => $data ) {



    echo $data['name'];

    echo "br";

    

    $subpage = $data['subpage'];

    if ( is_array( $subpage) ) {

        foreach ( $subpage as $_i => $sub ) {

                echo $sub['name'];    

                                echo "br";

        }

    }

}



/* OUTPUT ----



Main

--- Main SubPage 1

--- Main SubPage 2

Menu Item 2

Menu Item 3

... Menu Item 3 SubPage 1

... Menu Item 3 SubPage 2



*/

?>

Thanks abiko, that was what I was looking for. I just couldn't come up with it.

Cheers!