Hi! I’m stuck with a problem here. My aim is to create a CMS for navigation menu, that’s why I use database table to save name and id of a category, as well as its predecessor (for nested unordered lists). The problem is that I don’t know what should be the best technique (also the table structure, for example, should I save paths etc) in order to get from something like this:
There are two ways depending on how you want to go about it. The first is the most common but the second is usually faster unless you have abandoned records (ones without valid parent records).
The first is simply to a query for all items with parent = 0. I used parent simply because I’m lazy with the keyboard. Then, for each of those run subsequent queries to get the child records of these items, and so on. It’d be a recursive loop:
<?php
function getItemsByParent($ParentID){
$Query = MySQL_Query('SELECT * FROM items WHERE parent = 0'); //assuming MySQL
$Items = array();
while($row = mysql_fetch_assoc($Query)){
$row['Children'] = getItemsByParent($row['id']);
$Items[] = $row;
}
return $Items;
}
function getNestedItems(){
return getItemsByParent(0);
}
var_dump(getNestedItems());
That will get something similar but more extensive than what you asked for, so if you want to refine it go ahead
The second way is essentially getting ALL rows from the table and then processing it with PHP, but from a ‘making sense of it’ point of view I prefer the first method.
Rudy, the issue with that method is that you don’t know how far something will be down a list - it fixes the depth at which you can go. I’m wondering how it’d benchmark against a simpler query to just get all the rows in the table.
The looped query thing is less efficient - granted - but the question implies that the OP might not be completely comfortable with PHP so a structured approach helps the understanding.
My personal approach would be to collect the data and process it later, but for a system with basic needs doesn’t need such complication from the understanding-the-code side of things.
keissfootball, are you just recording for each record the id of its parent record? Have you considered using either the “Adjacency List Model” or “Modified Preorder Tree Traversal” for recording the relationship between the records (for recording the tree structure)?
My own preference is to use “Modified Preorder Tree Traversal” due to only needing 1 query to get the tree. See this SitePoint Article for more info on the two methods.
