Genius.
I was almost there in my own code, it was the _print_menu($items,
$parent_id) recursion that clinched it.
Many thanks (to all respondents) for your advice.
Cheers.
Bob.
-----Original Message-----
From: Larry Garfield
Sent: 25 May 2008 09:16
To: php-general@xxxxxxxxxxxxx
Subject: Re: Re: array recursion from database rows
On Saturday 24 May 2008, Chris W wrote:
> Bob wrote:
> > Hi.
> >
> > I have a database table I have created for navigation.
> >
> > The table fields are uid, parent_id, menu_name.
> >
> > Each entry is either a top level element with a parent_id of 0 or a
child
> > which has a parent_id that relates to the parent uid.
> >
> > What I am trying to do is recurse through a set of rows adding the
> > child(ren) of a parent to a multi-dimensional array.
> >
> > Does anyone know how I can do this, I've tried (unsuccessfully) to
> > traverse the rows to create this array but I keep failing miserably.
> >
> > This is probably very easy for the people on this list so I am hoping
> > someone could help me out.
>
> I recently wrote a function to do just that. My data structure is a
> little different than yours. My table is called menuitems and is
> designed to store menu items for many different menus. But I do use the
> same ParentID concept you described to link sub menus in. I just call
> my function recessively. Here is a slightly simplified version of my
> function. I replaced the standard html tags with [ and ] to avoid
> stupid email clients trying to encode it as an html message.
>
>
> function PrintMenu($MenuID, $ParentItemID)
> {
> $query = "SELECT * \n";
> $query .= "FROM `menuitem` \n";
> $query .= "WHERE `MenuID` = '$MenuID' AND `ParentItemID` =
> '$ParentItemID' \n";
> $query .= "ORDER BY `OrderBy` \n";
> //print "[pre>$query[/pre>\n";
> $result = mysql_query($query);
> QueryErrorLog($result, $query, __FILE__, __LINE__, __FUNCTION__,
> mysql_error(), mysql_errno(), 1);
> if(mysql_num_rows($result) > 0){
> print "[ul]\n";
> while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
> foreach($row as $TmpVar => $TmpValue){
> $$TmpVar = $TmpValue;
> }
> print "[li][a href='$URL']$Title[/a][/li]\n";
> PrintMenu($MenuID, $MenuItemID);
> }
> print "[/ul]\n";
> }
>
> }
That's bad, because it will run a variable number of SQL queries.
It's better to pull all of the data at once, and then recurse over that data
structure. To wit (off the cuff and not tested, using PDO syntax for the
database):
function print_menu() {
$items = array();
$db = db_connection();
$result = $db->query("SELECT menu_id, parent_id, name FROM menu_items
ORDER
BY name");
foreach ($result as $record) {
$items[$record->parent_id][$record->menu_id] = $record;
}
$output = _print_menu($items, 0);
return $output;
}
function _print_menu($items, $parent_id) {
$output = '';
if (!empty($items[$parent_id])) {
foreach ($items[$parent_id] as $menu_id => $item) {
$output .= '<li>' . $item->name . _menu_print($items,
$menu_id) . '</li>';
}
}
return $output ? "<ul>$output</ul>" : '';
}
That way you run only one SQL query, and by pre-grouping you can reduce your
iterations as well.
--
Larry Garfield AIM: LOLG42
larry@xxxxxxxxxxxxxxxx ICQ: 6817012
"If nature has made any one thing less susceptible than all others of
exclusive property, it is the action of the thinking power called an idea,
which an individual may exclusively possess as long as he keeps it to
himself; but the moment it is divulged, it forces itself into the possession
of every one, and the receiver cannot dispossess himself of it." -- Thomas
Jefferson
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
