How to pull grandchildren from database

Question

I want to pull out menu items from MySQL.

Main menu           id=1, parentid=0
-Contact us         id=2, parentid=1
-Music              id=3, parentid=1
 --Rock             id=8, parentid=3
 --Classic          id=9, parentid=3
-Car                id=4, parentid=1
  --Toyota          id=5, parentid=4,
  --Ford            id=6, parentid=4,
  --Honda           id=7, parentid=4

Other menu          id=10, parentid=0
-Othermain          id=11, parentid=10
  --submenu         id=12, parentid=11

etc.

I can pullout data from id=1 to 4 and display by "...where parentid=1" etc. However this pulls out only the top level.

But I want to pullout all the data including submenu for each menu(main menu) as well.

Could anyone tell me how to write a query in MySQL for this please?

Thanks in advance.

Answer 1

The fastest way is to fetch all elements from the table and build menu tree in the code side.

Answer 2

You need to implement recursion to make repeated calls to the database to retrieve all children. You will have to replace my database abstraction layer implementation with your own but the concept is the same.

memcache solution

function generateTree($parentid = 0, &$tree) {
    $sql = sprintf('SELECT * FROM navigation WHERE parentid = %d', $parentid);
    $res = $this->db->results($sql);
    if ($res) {
        foreach ($res as $r) {
            // push found result onto existing tree
            $tree[$r->id] = $r;
            // create placeholder for children
            $tree[$r->id]['children'] = array();
            // find any children of currently found child
            $tree = generateTree($r->id, $tree[$r->id]['children']);
        }
    }
}

function getTree($parentid) {
    // memcache implementation
    $memcache = new Memcache();
    $memcache->connect('localhost', 11211) or die ("Could not connect"); 
    $tree = $memcache->get('navigation' . $parentid);
    if ($tree == null) {
        // need to query for tree
        $tree = array();
        generateTree($parentid, $tree);

        // store in memcache for an hour
        $memcache->set('navigation' . $parentid, $result, 0, 3600);
    }
    return $tree;
}

// get tree with parentid = 0
getTree(0);

non memcache solution

function generateTree($parentid = 0, &$tree) {
    $sql = sprintf('SELECT * FROM navigation WHERE parentid = %d', $parentid);
    $res = $this->db->results($sql);
    if ($res) {
        foreach ($res as $r) {
            // push found result onto existing tree
            $tree[$r->id] = $r;
            // create placeholder for children
            $tree[$r->id]['children'] = array();
            // find any children of currently found child
            $tree = generateTree($r->id, $tree[$r->id]['children']);
        }
    }
}

// get tree with parentid = 0
$tree = array();
$parentid = 0;
generateTree($parentid, $tree);

// output the results of your tree
var_dump($tree); die;

The above is untested so if anybody catches an error please let me know or feel free to update.