help on threaded category Hierarchy with mysql

Question

enter image description here

GID    name                     parent_GID     status
1      HQ                                      0 # 0 means its a category
2      Blog                     1              x # x some administration stuff
3      Feedback & Support       1              x
5      Service                                 0
6      Web                      5              x
7      Advertising              6              x
8      Others                   5              x

to be something like ( 3 level )

HQ
    Blog
    Feedback & Support
Service
    Web
        Advertising
    Others

what i have done so far

SELECT * FROM groups WHERE status = 0 # get categories

prints

HQ
Services

so we can do something like

# fetch categories
# get the GIDs
# fetch with foreach GID
  HQ # with GID 1 so search if there is child 1
      # SELECT * FROM groups WHERE parent_GID = 1
      Blog
      Feedback & Support
  Service # with GID 5 so search if there is child 5
      # SELECT * FROM groups WHERE parent_GID = 5
      Web
      Others

the problem above is its olny 2 level Hierarchy.

any suggestion to do a level 3 fetch all ?

thanks!

Use a materialized path: http://stackoverflow.com/questions/7276119/querying-sqlite-tree-structure/7277073#7277073 — mu is too short, Sep 04 '11 at 04:52

score 1 · Answer 1 · edited May 23 '17 at 10:30

A previous answer of mine might prove a good starting point:

Generating Depth based tree from Hierarchical Data in MySQL (no CTEs)

Hint:

change the following statement

insert into hier select parent_cat_id, cat_id, v_depth from categories where cat_id = p_cat_id;

to

insert into hier select parent_cat_id, cat_id, v_depth from categories where parent_cat_id is null;

and forget about passing in a starting parent_cat_id !

Hope this helps :)

help on threaded category Hierarchy with mysql

1 Answers1