[eluser]Wuushu[/eluser]
[quote author="oddman" date="1211904891"]Bingo! It is a little bit of a performance hit, having to loop twice - but its worth it for code maintenance and scalability.
In order to do it as one query, you'd have all categories in one table, then have a field called category_id, so for example, the table structure would look like something like:
CREATE TABLE categories (
id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
category_id INT(11) UNSIGNED,
name VARCHAR(255)).etc.
Then when pulling the categories, in the model you might have a function that looks like this:
Code:
public function get_categories($parent_id = null)
{
$tree = array();
// useful for getting selected categories of a parent
if (!is_null($parent_id)) {
$query = $this->db->get_where('categories', array('category_id' => $parent_id));
}
else {
$query = $this->db->get('categories');
}
// loop through the results and create the tree
foreach ($query->result_array() as $row) {
$tree[$row['category_id']][] = $row; // appending a new category item to the parent array (this will be demonstrated later)
}
return $tree;
}
Now, we have a list of all categories as part of a tree, which we can call on at any point. So, once you've assigned the data you need to the view, all you need to do is this:
Code:
foreach ($cat_data[0] as $cat) { // i'm assuming here that top-level parents have a category_id of 0. This could very well simply be null or -1, or whatever best suits your application
echo '<h2>'.$cat['name'].'</h2>';
if (isset($cat_data[$cat['id']])) { // checking to see if we have children, if we do - loop through those and grab them!
foreach ($cat_data[$cat['id']] as $child_cat) {
echo '<h3>'.$child_cat['name'].'</h3>';
echo '<p>'.$child_cat['body'].'</p>';
}
}
}
Now, you might be saying "wow, that's a lot of looping". Maybe so - but it's only one query. As a result, it will be a 100x faster than doing a subquery each time you find a category. One query is possible with the table layout you're proposed, however it's a bit trickier and means a lot more data is being retrieved.
Hope that helps
PS: one more thing - that view code is best done as a recursive function. It can also support an unlimited number of sub categories and levels.[/quote]
What if $child_cat has a child? Does the view foreach-function take into account and print those out as well?
