Strange behaviour in hierarchical menu?

Question

The function is simple:

 function showMenu( $level = 0 ) {

    $sql = "SELECT * FROM `menus` WHERE `submenu`=".$level;

    $result = mysql_query( $sql );

    echo "<ul>";

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

            echo "<li>". $node['name'];

            $sql_ = "SELECT * FROM `menus` WHERE `submenu`=".$node['id'];

            $hasChild = mysql_fetch_array( mysql_query( $sql_ ) ) != null;

            if ( $hasChild ) {

                showMenu( $node['id'] );

            }

            echo "</li>";

        }

    echo "</ul>";

 }

But the results when I'm using the level 1 or over is strange. For example:

showMenu( 1 );

will return me all the items, except first items.

Answer 1

function showMenu( $level = 0 ) {

    $sql = "SELECT * FROM `menus` WHERE `submenu`=".$level;

    $result = mysql_query( $sql );

    echo "<ul>";
      $rows = "";
        while ( $node = mysql_fetch_array( $result ) ) {

            echo "<li>". $node['name'];

            $sql_ = "SELECT * FROM `menus` WHERE `submenu`=".$node['id'];

            $execute = mysql_query( $sql_ );

            $rows = mysql_num_rows($execute);
            if ( $rows>0 ) {

                showMenu( $node['id'] );

            }

            echo "</li>";

        }

    echo "</ul>";

 }

Answer 2

First, i think great coders never copy and paste code. its meant to fail.

that why for my opinion you have to get all menus (including the submenus). its better that way, because that you sending only one packet to sql.

now that you have all menu in object. (hey, we are in year 2013: use mysqli object oriented) you can check every time in simple if statement weather to build new <ul> tag.

Hope it helps.