Building a php nested lists from database tables

Question

I have 3 tables in a database, which I would like to join in order to build a 3 level nested list (which will be used as part of a menu). I have so far figured out how to select the data from the tables from the following tables. I would like some help with generating the 3rd level of the menu, in the php section.:

SQL Tables:

page_list : pageID (primary_key) page_name pageURL
level2_menu : GeneralID(primary_key) gener_name pageID(foreign_key);
level3_menu : deepID(primary_key) generalID (foreign_key) deep_title

using:

<?php 
get_menu("SELECT page_list.pageID as pageID, page_list.page_name as page_name, page_list.pageURL as pageURL, level2_menu.GeneralID as GeneralID, level2_menu.gener_name as general_name, level3_menu.deepID as deepID, level3_menu.deep_title as deep_title
          FROM page_list
          LEFT OUTER JOIN level2_menu ON page_list.pageID = level2_menu.pageID
          LEFT OUTER JOIN level3_menu ON level2_menu.GeneralID = level3_menu.generalID
          UNION
          SELECT page_list.pageID as pageID, page_list.page_name as page_name, page_list.pageURL as pageURL, level2_menu.GeneralID as GeneralID, level2_menu.gener_name as general_name, level3_menu.deepID as deepID, level3_menu.deep_title as deep_title
          FROM page_list
          RIGHT OUTER JOIN level2_menu ON page_list.pageID = level2_menu.pageID
          RIGHT OUTER JOIN level3_menu ON level2_menu.GeneralID = level3_menu.generalID
          ORDER BY pageID desc
          ");

 ?>

This gives the following sql result: http://sqlfiddle.com/#!9/927a0

+--------------------+------------+----------+-----------+---------------+--------+------------+
| pageID Ascending 1 | page_name  | pageURL  | GeneralID | general_name  | deepID | deep_title |
+--------------------+------------+----------+-----------+---------------+--------+------------+
|                  7 | get quote  | 0        | NULL      | NULL          | NULL   | NULL       |
|                  4 | tjanster   | info.php | 9         | Title         | NULL   | NULL       |
|                  3 | products   | info.php | 5         | Title 2       | 8      | Subtitle   |
+--------------------+------------+----------+-----------+---------------+--------+------------+

As well as getting the data for level 1 list and level 2 list, using:

function get_menu($sql)
{
    include 'connect.php';
    $result = $conn->query($sql);
    if ($result->num_rows > 0)
        ;
    $current_album = "";
    echo "<div id='menu'><div class='menu_pos'><ul class='main-navigation'>\n";
    while ($row = $result->fetch_assoc()) {
        if ($current_album <> "" && $current_album <> $row['pageID'])
            echo "</ul></li>";

        if ($current_album <> $row['pageID']) {
            echo "<li id='qoute'><a href='#'>" . $row['page_name'] . "</a><ul>\n";
            $current_album = $row['pageID'];
        }

        if ($row['GeneralID'] == 'NULL') {
            echo "bob";
        } else {
            echo "<li><a href='#'>" . $row['general_name'] . "</a></li>\n";
        }

    }
    echo "</ul></div></div>";
}

This results in the The first menu example shown here which is a 2 level list, however I would like 3 level list, like the second menu example, shown in the jsfiddle.

Here you go: jsfiddle.net/981f8bgs that covers all the data types that may be generated. — ChriChri
– ChriChri, Commented Dec 6, 2015 at 23:55

phpnederland.nl · Accepted Answer · 2015-12-14 14:12:18Z

First of all it is a tough way to do things. Raw SQL / RAW HTML / RAW PHP. Templating (twig for example), DB helper (Doctrine for example) can make a magic.

But to answer your question - this might work:

First: I would suggest changes in SQL

SELECT 
page_list.pageID as pageID, page_list.page_name as page_name, page_list.pageURL as pageURL, 
level2_menu.GeneralID as GeneralID, level2_menu.gener_name as general_name, 
level3_menu.deepID as deepID, level3_menu.deep_title as deep_title
FROM page_list
    LEFT OUTER JOIN level2_menu ON page_list.pageID = level2_menu.pageID
    LEFT OUTER JOIN level3_menu ON level2_menu.GeneralID = level3_menu.generalID
ORDER BY pageID DESC, GeneralID DESC, deepID DESC

Second: split of preparing menu and outputmenu.

function get_menu($sql) {
// the final output is in $output
// @var string $output
$output = "";

include 'connect.php';
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    // constructing the structure of menu

    $current_album = null;
    $current_general_id = null;

    $menu = [];
    while ($row = $result->fetch_assoc()) {
        // checking if top level menu is already added
        if (!array_key_exists($row['pageID'], $menu)) {
            $menu[$row['pageID']] = [
                'id' => $row['pageID'],
                'name' => $row['page_name'],
                'url' => $row['pageURL'],
                'children' => []
            ];                  
        }
        $currentTopMenu = $menu[$row['pageID']];

        // checking if second level is defined and already exists
        if (!array_key_exists($row['GeneralID'], $currentTopMenu['children'])) {
            $sLevel = [
                'id' => $row['GeneralID'],
                'name' => $row['general_name'],
                'url' => '',
                'children' => []
            ];      
            $menu[$row['pageID']]['children'][$row['GeneralID']] = $sLevel;
        }
        $currentSecondMenu = $menu[$row['pageID']]['children'][$row['GeneralID']];

        // last level of menu
        if ($row['deepID']) {
            $menu[$row['pageID']]['children'][$row['GeneralID']]['children'][$row['deepID']] = [
                'id' => $row['deepID'],
                'name' => $row['deep_title'],
                'url' => '',
                'children' => []
            ];
        }
    }

    // making an output -> should be moved to the template
    $output .= "<div id='menu'><div class='menu_pos'><ul class='main-navigation'>\n";
    foreach ($menu as $menuTopItem) {
        $output .= "<li class='first'><a href='" . $menuTopItem['url'] . "'>" . $menuTopItem['name'] . "</a><ul>\n";

        foreach ($menuTopItem['children'] as $secondMenuItem) {
            $output .= "<li class='second'><a href='" . $secondMenuItem['url'] . "'>" . $secondMenuItem['name'] . "</a><ul>\n";
            foreach ($secondMenuItem['children'] as $thirdMenuItem) {
                $output .= "<li class='third'><a href='" . $thirdMenuItem['url'] . "'>" . $thirdMenuItem['name'] . "</a></li>\n";
            }
            $output .= "</ul></li>";
        }

        $output .= "</ul></li>";            
    }
    $output .= "</ul></div></div>";
}

echo $output;
// or
return $output;
}

Collectives™ on Stack Overflow

Building a php nested lists from database tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related