Categories/ Subcategories/ Items recursive?

PHP
1

Hi guys!
I’ve been trying for a while to display the Categories with their Subcategories and Items, but it seems impossible with my PHP knowledge. Your help will be really appreciate it!

My DB structure:

categories:
|id|name|description|parent_id|type|status|
-type: enum(‘general’,‘business’).
The leafs of ‘general’ type category are ‘articles’
The leafs of ‘business’ type category are ‘companies’

articles:
|id|title|content|parent_id|status|

companies:
|id|name|description|content|parent_id|status|

What I want to achieve:

About [type=general, parent_id=0]

[INDENT]Article1
Article2[/INDENT]

News [type=general, parent_id=0]

Category1

[INDENT][INDENT]Article1
Article2[/INDENT][/INDENT]

Category2

[INDENT]Article1[/INDENT]

Business Directory [type=business, parent_id=0]

Category1

[INDENT]Subcat1-1[/INDENT]

[INDENT][INDENT][INDENT]Company1
Company2
Company3[/INDENT][/INDENT][/INDENT]

[INDENT]Subcat1-2[/INDENT]

[INDENT][INDENT]Company1[/INDENT][/INDENT]

Category2

[INDENT]Subcat2-1[/INDENT]

[INDENT][INDENT]Company1[/INDENT][/INDENT]

[INDENT][INDENT]Company2[/INDENT][/INDENT]

[INDENT][INDENT]---------- and so on…[/INDENT][/INDENT]

Roadie

2

This is a database issue, but the thing to google is ‘nested set’. There is a good article on the mysql site here: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

3

“nested set” is not the same data model as roadie’s, which is adjacency model

why make him change all the code he already wrote for insertions etc.?

:slight_smile:

4

How should I do it then?

Roadie

5

Well first of all, let’s throw this out there: You’re going to need at least 2 queries, because your tables are not strictly related/combinative. Personally i’d throw the Category table away and add entries to the other tables. Then sort them out in PHP if they dont contain data(children). If you want to keep your Articles table for both About and News, you’ll need a way to differentiate between the types (an int field, or if you REALLY feel the need, extend the enum. PS. Enum is evil.)

6

However; keeping to your current schema…

Select Articles WHERE parent_id = 0. Those are the About articles.
Select Categories WHERE parent_id = 0 And Type = general
Store the names of the id’s and categories in PHP. Smash the ID’s together seperated by commas.
Select Articles WHERE parent_id IN($theidlist). Those are the News Articles. Foreach of these, put them in a second dimension of the categories. You’re now done with News.
Select Categories WHERE type = business ORDER BY parent_id
Sort these out, and multi-dimension the array as needed.
Select Companies;
Foreach company assign it to a new dimension of the array below the appropriate parent. Alternately: Create a tree which each node contains an element “children”. Append the child’s ID to the parent’s ‘children’ element.
Recursively output the tree.

7

you can do it all with one query –

SELECT categories.name AS category_name
     , NULL            AS subcategory_name
     , NULL            AS subsubcat_name
     , articles.title  AS title_or_descr
  FROM categories
LEFT OUTER
  JOIN articles
    ON articles.parent_id = categories.id
 WHERE type = 'general'
   AND parent_id = 0
UNION ALL
SELECT categories.name
     , subcategories.name
     , NULL
     , articles.title
  FROM categories
LEFT OUTER
  JOIN categories AS subcategories
    ON subcategories.parent_id = categories.id
LEFT OUTER
  JOIN articles
    ON articles.parent_id = subcategories.id
 WHERE type = 'general'
   AND parent_id = 0
UNION ALL
SELECT categories.name
     , subcategories.name
     , subsubcat.name
     , companies.description
  FROM categories
LEFT OUTER
  JOIN categories AS subcategories
    ON subcategories.parent_id = categories.id
LEFT OUTER
  JOIN categories AS subsubcat
    ON subsubcat.parent_id = subcategories.id
LEFT OUTER
  JOIN companies
    ON companies.parent_id = subsubcat.id
 WHERE type = 'business'
   AND parent_id = 0
ORDER
    BY category_name
     , subcategory_name
     , subsubcat_name

this query will produce the following results -


About    NULL      NULL      Article1
About    NULL      NULL      Article2
News     Category1 NULL      Article1
News     Category1 NULL      Article2
News     Category2 NULL      Article1
Business Category1 Subcat1-1 Company1
Business Category1 Subcat1-1 Company2
Business Category1 Subcat1-1 Company3
Business Category1 Subcat1-2 Company1
Business Category2 Subcat2-1 Company1
Business Category2 Subcat2-1 Company2
8

Yea so i had some spare time… here’s how i’d do the business section using your schema. There’s probably a better way, an OOP way… and then there’s my way :stuck_out_tongue:


$tree = new array(array("children" => array()));
$cats = $db->query("SELECT id,name,parent_id FROM categories WHERE type = 'business' ORDER BY parent_id");
while ($cat = $cats->fetch_array()) {
  $tree[$cat['parent_id']]['children'] .= (count($tree[$cat['parent_id']]['children']) == 0) ? "" : ",";
  $tree[$cat['parent_id']]['children'] .= $cat['id'];
  $tree[$cat['id']] = array("name" => $cat['name'], "children" => array());
}
//Now I have all my catgories, lets pull the associated businesses....
$cats = $db->query("SELECT id,name,parent_id FROM companies ORDER BY parent_id");
while ($cat = $cats->fetch_array()) {
  $tree[$cat['parent_id']]['companies'][] = $cat;
}
//Now My tree is full.
echo "Business Directory<ul>";
treeexplode($tree[0]);
echo "</ul>";

function treeexplode($node) {
  echo "<li>".$node['name']."</li>";
  echo "<ul>";
  foreach($node['children'] AS $value) {
     treeexplode($tree[$value]);
  }
  echo "</ul>";
  foreach($node['companies'] AS $value) {
    echo "<li>".$value['name']."</li>";
  }
  return true; //Redundant, but eh.
}
9

r937:
Do you feel it’s more efficient to do it that way? What if there are subcategories inside subcategories?

10

Thank you guys!

I did a print_r() of r937’s query and this is the result:


Array
(
    [0] => Array
        (
            [category_name] => About
            [subcategory_name] =>
            [subsubcat_name] =>
            [title_or_descr] => First Article in About
        )

    [1] => Array
        (
            [category_name] => About
            [subcategory_name] =>
            [subsubcat_name] =>
            [title_or_descr] => Second Article in About
        )

    [2] => Array
        (
            [category_name] => Business Directory
            [subcategory_name] => Cars
            [subsubcat_name] => Sub-cat of Cars
            [title_or_descr] => Description of this sub cat....

        )

    [3] => Array
        (
            [category_name] => Business Directory
            [subcategory_name] => Computers
            [subsubcat_name] =>
            [title_or_descr] =>
        )

    [4] => Array
        (
            [category_name] => Business Directory
            [subcategory_name] => Restaurants
            [subsubcat_name] =>
            [title_or_descr] =>
        )

    [5] => Array
        (
            [category_name] => News&Events
            [subcategory_name] =>
            [subsubcat_name] =>
            [title_or_descr] => First in News&Events
        )

    [6] => Array
        (
            [category_name] => News&Events
            [subcategory_name] =>
            [subsubcat_name] =>
            [title_or_descr] => Second in News&Events
        )

    [7] => Array
        (
            [category_name] => Another Category
            [subcategory_name] =>
            [subsubcat_name] =>
            [title_or_descr] => First in Another Category
        )

    [8] => Array
        (
            [category_name] => Another Category
            [subcategory_name] =>
            [subsubcat_name] =>
            [title_or_descr] => Second in Another Category
        )

)

It’s not exactly what I’m expecting. I want ALL the subcats to be grouped into another array, under each category. How should I do it?

StarLion: How can I show the articles/ companies under the leaf categories?

11

I dont understand what you mean… You want to take the tree and multi-dimensionalize it for no apparant reason? Okay… so make treeexplode() put the array into existance.

PS: Need to change


  $tree[$cat['id']] = array("name" => $cat['name'], "children" => array());

into


  $tree[$cat['id']]['name'] = $cat['name'];

to avoid overwriting the children array if the nodes arnt inorder.

12

I was thinking to build my application with Codeigniter.

In order to do that I need to get the tree into an multi-dimensional array and pass it to the View.

Still don’t know if its a good idea…

Thanks a lot for your replies!
Roadie

13

yes, i do, and yes, there are subcategories within categories

i have given 3 separate SELECTs in the UNION query, and each one of them is a different “path” –

  • root general category to article
  • root general category to subcategory to article
  • root business category to subcategory to subsubcategory to company

these three paths reflect the sample data described in post #1

simple, yes?

i don’t know about php arrays (i don’t do php) but i did give the sample result set produced by my UNION query and in order to display the results you would simply loop over the rows of the result set and display them accordingly

notice the NULL values in some of the columns, these are necessary so that the three different paths produce the same structure of row to allow for compatibility of the SELCTs in the UNION query

14

r937: My point was what if there’s another depth layer of the tree? Two? 4? At what point does it become less efficient to draw a bunch of “NULL” or Category/subcategory/subcatorgy/sub… for each row, as opposed to only drawing 1 integer value per row, and running the extra query? to me, it seems to be more efficient to just pull the data in 4 queries, than one overinflated query. I could be wrong.

roadie7: A numerical multidimensional array? Ok…


function treeexplode($node) {
  foreach($node['companies'] AS $value) {
     $node[] = $value;
     //If you need to unfurl the company row, then do it here using whatever schema you had in mind.
  }
  foreach($node['children'] AS $value) {
     $node[] = treeexplode($value);
  }
  unset($node['children']);
  unset($node['businesses']);
  return($node);
}

$array = treeexplode($tree[0]);
15

at the point where your path through the tables does not reflect actual data

if you look again at the three paths i covered, you will see that there are no sub-subcategories for articles, so catering for them would be less efficient

and i do not agree at running a single UNION query is “overinflated”

also, running a single UNION query is likely going to be faster than your 4 queries

:slight_smile:

16

I’d call anything that returns more than necessary overinflated, personally, even if all the extra data is a bunch of nulls. (and it’s not, even.)

Consider that all PHP needs to know in order to create a tree structure is the parent_id. It doesnt care if the thing in level 4 of the tree is attaches to the number 0 level 1 item or the number 1 level 1 item. The rest of the tree is already storing that information.

I defer to your guru status when it comes to SQL… but it just seems silly to me.