woe2you Posted May 13, 2008 Share Posted May 13, 2008 I've been hacking away at php for a couple of years, but I have no coding background and I'm not particularly good at it. I recently came to rewrite some navigation code for a site I'm doing which uses a database thus: category_id child_of category_name category_description category_bg to store hierarchical data. I originally had a new query running for every new sublevel of the hierarchy, and I woke up one morning in an inspired mood and figured it would be much easier to work with if I just called the whole thing into one big multidimensional array, thus: function get_categories() // pulls all categories from the database into a two-dimensional associative array for use by other functions { $query = "SELECT DISTINCT * FROM categories ORDER BY child_of, category_name"; $result = mysql_query($query) or die(mysql_error()); if(mysql_num_rows($result) > 0) { while($row = mysql_fetch_assoc($result)) { $categories[$row['child_of']][] = $row; } } return $categories; } But now I'm stuck - how can I find an array in this great giant nested mess by its key, when I don't know how far down the hierarchy it is? I want to be able to take any category from the querystring (for example, if someone's hit a landing page somewhere deep in the site) and work backward from that key up the array to the root in order to generate a breadcrumb trail, etc. I don't want to be thrashing the DB all the time, like I used to: function get_breadcrumb() //temporary function to get breadcrumb trail into array { if(isset($_GET['category'])) { $lookup = $_GET['category']; } else { $lookup = 0; } while($lookup > 0) { $query = "SELECT * FROM categories where category_id = '$lookup'"; $row = mysql_fetch_assoc(mysql_query($query)); $breadcrumb[$row['category_id']] = $row['category_name']; $lookup = $row['child_of']; } return $breadcrumb; } Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted May 13, 2008 Share Posted May 13, 2008 You might benefit from taking a look at this article from the mysql website about storing and working with hierarchical data. Quote Link to comment Share on other sites More sharing options...
woe2you Posted May 13, 2008 Author Share Posted May 13, 2008 I tried to look at tree traversal before, but to me it seems even more complicated. With the adjacency list, at least I can understand the data model in my head - which is half the issue. When I was using this code before, I managed to simplify the code I wrote for drop-down menus (nested unordered lists) from needing a query for each list to one query: function draw_menu() // displays top-level product menu { $query = "SELECT DISTINCT category_id, category_name, child_of, category_description FROM categories ORDER BY child_of, category_name"; $result = mysql_query($query); while($row = mysql_fetch_assoc($result)) { // create two-dimensional array of menus $menus[$row['child_of']][] = $row; } draw_ul(0, $menus); } function draw_ul($child_of, $menus) // displays nested menus { // check if top-level menu if($child_of == 0) { echo "<div class=\"blockshadow\">\n"; } else { echo "\n<div class=\"subshadow\">\n"; } echo "<ul>\n"; foreach($menus[$child_of] as $key => $value) { draw_li($value, $menus); } echo "</ul>\n"; echo "</div>\n"; } function draw_li($item, $menus) // draws each menu item { $desc = excerpt_text(strip_tags($item['category_description']), 70); echo "<li><a href=\"http://" . $_SERVER['HTTP_HOST'] . "/products.php?category=" . $item['category_id'] ."\" title=\"" . $desc . "\">" . $item['category_name'] . "</a>"; // check if item has a submenu if(isset($menus[$item['category_id']])) { draw_ul($item['category_id'], $menus); } echo "</li>\n"; } - that took me a lot of effort, and when I'm halfway to understanding it I don't want to throw it all out and start fresh. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.