MySQL hierarchical data into multidimensional array

[remb]

Hi all,

This is my first post on phpbuilder.com forums and hopefully I'm not posting this in the wrong section...
I found this tutorial here: http://codewalkers.com/tutorials/89/1.html and somehow it's exactly what I was looking for except I would need to dynamically generate the menu data from a MySQL database into the multidimensional array. Unfortunately I'm not that proficient with PHP and this kind of coding is out of my league. Can you please advise? I would be very grateful.

Thanks a lot for your time,

Best,
Rem.

[wilku]

Maybe this will help you:
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
It's from MySql manual, describing how to store hierarchical data in a flat database. The actual php code will depend on database scheme you'll apply in your project.

[leatherback]

Hope this will get you started, it is a menu-builder that I wrote a while ago. It works. Not sure about glitches (Been too long..):

<?
  $menuquery = "select * from menu order by M_sortorder asc";

$result=mysql_query($menuquery) or die('An error occured retrieving data');

$listing = Array();

while($row = mysql_fetch_array($result))
  { 
  // This id: The page identifier
  // Path: the link base path
  // parent id: table ID of the parent in the tree
  // category label: Label in the menu
  // M_link: Actual page name (I think! check code)

  $listing[] = array("This_id"        => $row['M_page_nr'], 
                     "path"           => $row['M_path'],
                     "parent_id"      => $row['M_p_nr'],    

                     "category_title" => $row['M_label'], 
                     "link"           => $row['M_link']
                     );
  }

function get_expanded($parent, $level, $array, $currentmain)
{
global $pageid; // Just lazy: Did not want to pass the current page id
$selectbranch = '';

foreach($array AS $node)
{
if($node['parent_id']==$parent)
  {
  if($node['parent_id'] == 0)  // Set the branch we are evaluating
    {
    $currentmain = $node['This_id'];
    }

  if($pageid <> $node['This_id'])
    {
    $ttt = get_expanded($node['This_id'], ($level + 1), $array, $currentmain);
    if($ttt <> "")
      {
      $selectbranch = $ttt;
      }
    }
 else
    {
    $selectbranch = $currentmain;
    }
  }
}

return $selectbranch;

} // end function 

function display_tree($parent, $level, $array, $path, $expanded, $isexpanded)
{
global $pageid, $countrylinks; 
$html = "";

foreach($array AS $node)
{
if($node['parent_id']==$parent)           // The current entry is a subbranch
  {  

  if($node['parent_id'] == 0)             // Reset the expanded branch
    {
    $isexpanded = -1;
    }

  if(($node['This_id']) == $expanded  && ($node['This_id'] <> 12))     // SET the expanded branch
    {
    $isexpanded = 1;
    }

  if($pageid <> $node['This_id'])
    {
    if(($isexpanded <> -1) or ($level <=0))
      {
      $html .= "
        <a class=\"menu".$level."\" href=\"$path$node[path]$node[link]\">".$node['category_title']."</a>
        ";
      $html .= display_tree($node['This_id'], ($level + 1), $array, $path, $expanded, $isexpanded);
      }
    }
 else
    {
    $html .= "
    <a id=\"current\" class=\"menu".$level."\" href=\"$path$node[path]$node[link]\">".$node['category_title']."</a>
    ";
    $html .= display_tree($node['This_id'], ($level + 1), $array, $path, $expanded, $isexpanded);
    }
  }
} 

//  }

  return $html;

} // end display list


// Now use it
$opened = get_expanded(0, 0, $listing, -2);

$menu = display_tree(0, 0, $listing, $baseurl, $opened, -1);  

$menu .="<a class=\"menu0\" href=\"javascript:window.print()\">Print Page</a>" ;
?>

[remb]

Thanks a lot guys, I really appreciate your fast replies. This is a great forum!!!
leatherback, I will definitively give your code a try, really thanks! 🙂