SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Member
    Join Date
    Aug 2006
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Creating a category tree from mysql database.

    Hi! I am building a php classified listing website and would like some help on how to create a category and subcategory tree to guide my visitors to the correct category they would like to view.

    Database details:
    I intend to use only one table - CATEGORIES TABLE with the following fields.

    | ID | name | ParentID |

    +----------------+---------|

    | 1 | Cat1 | 0 |

    | 2 | Cat2 | 0 |

    | 3 | Cat3 | 0 |

    | 4 | Subcat1 | 1 |

    | 5 | Subcat2 | 2 |

    | 6 | Subcat3 | 1 |

    | 7 | Subcat4 | 3 |

    | 8 | Subcat5 | 3 |

    etc


    This is the result i'm looking for:




    Your help would be much appreciated.

    Halfdogg

  2. #2
    SitePoint Zealot
    Join Date
    Apr 2006
    Posts
    158
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    use recursion. pass the id as parameter. check if that id is a parent of other category. if yes call the function or stop.

  3. #3
    SitePoint Member
    Join Date
    Aug 2006
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not that experienced in php. I need someone to walk me thru. Thanks.

  4. #4
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Are your Categories to be polyhierarchical?

    Would the same article appear in say, Business, Regional and Computers at the same time?

  5. #5
    SitePoint Member
    Join Date
    Aug 2006
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi cups,
    No, they are not polyhierarchical. The main idea is that a subcategory is linked to only one main category so the category is listed as the head then it's children listed below it separated by commas.

  6. #6
    SitePoint Zealot
    Join Date
    Apr 2006
    Posts
    158
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    <?php
    function multilevel($id)
    {
        
    $rs1=mysql_query("select * from category where pid='$id'");
        if(
    mysql_num_rows($rs1)==0) return;
        echo 
    "<ul>";
        while(
    $row1=mysql_fetch_array($rs1))
        {
        echo 
    "<li>".$row1['name']."";
        
    multilevel($row1['id']);
        }
            echo 
    "</ul>";


    }
    mysql_connect("*********","****","****");
    mysql_select_db("test");
    $rs=mysql_query("select * from category where pid='0'");
    echo 
    "<ul>";
    while(
    $row=mysql_fetch_array($rs))
    {
        echo 
    "<li>".$row['name']."";
        
    multilevel($row['id']);
    }
    echo 
    "</ul>";
    ?>

  7. #7
    SitePoint Evangelist cronsrcs's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    500
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What is the advantage/disadvantage of this method (ie 1 db table) vs having a table of Categories and a SubCategories table....

    I normally use 2 table -eg Links and Link Types - I select the link details (url etc) and also the title of the link type and order by the link type....Then loop through all results only echoing the Link type heading if the link type is different to the last row that was processed....

    What do people think?
    www.silver-rocket.com
    Creative Solutions for this planet and beyond...
    www.onelifeonebus.com
    Fear fading away, not burning out.

  8. #8
    SitePoint Zealot
    Join Date
    Apr 2006
    Posts
    158
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    suppose you r building a shopping cart and and you don't know the exact number of subcategories then this technique comes handy. it's specially for multiple sub-categories.

  9. #9
    SitePoint Evangelist cronsrcs's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    500
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Jito,

    can you ellaborate a little more? I am not sure that I understand

    Thanks
    www.silver-rocket.com
    Creative Solutions for this planet and beyond...
    www.onelifeonebus.com
    Fear fading away, not burning out.

  10. #10
    SitePoint Zealot
    Join Date
    Apr 2006
    Posts
    158
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    think of a situation when you dont know how many sub category are there. if you want to leave the control in the hands of administrator, when administrator can add categories, subcategories, its difficult to create a table each time specially when you r creating a subcategory under a subcategory.
    i hope this explains, or you can question if u want something else.

  11. #11
    SitePoint Evangelist
    Join Date
    Mar 2006
    Location
    Sweden
    Posts
    451
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jito
    PHP Code:
    <?php
    function multilevel($id)
    {
        
    $rs1=mysql_query("select * from category where pid='$id'");
        if(
    mysql_num_rows($rs1)==0) return;
        echo 
    "<ul>";
        while(
    $row1=mysql_fetch_array($rs1))
        {
        echo 
    "<li>".$row1['name']."";
        
    multilevel($row1['id']);
        }
            echo 
    "</ul>";


    }
    mysql_connect("*********","****","****");
    mysql_select_db("test");
    $rs=mysql_query("select * from category where pid='0'");
    echo 
    "<ul>";
    while(
    $row=mysql_fetch_array($rs))
    {
        echo 
    "<li>".$row['name']."";
        
    multilevel($row['id']);
    }
    echo 
    "</ul>";
    ?>
    Recursing a function that collects data from the database like that is - imo - a big no no. You should read this article:
    http://www.sitepoint.com/article/hie...data-database/

  12. #12
    SitePoint Zealot
    Join Date
    Apr 2006
    Posts
    158
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes i know it's slow but i don't know any alternative solution.

  13. #13
    SitePoint Evangelist cronsrcs's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    500
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Jito - thanks for that - originally i thought that you meant the number of subcategories, but in fact you are actually referring to the levels in the hierarchy - categories, subcategories, sub-subcategories etc.

    I understand - thanks again.

    I will take a look at the article suggested by wysiwyg now.

    Cheers
    www.silver-rocket.com
    Creative Solutions for this planet and beyond...
    www.onelifeonebus.com
    Fear fading away, not burning out.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yes, recursion can be slow

    have a look at this article -- Categories and Subcategories

    the mockup shown in post #1 will require only one LEFT OUTER JOIN, with GROUP_CONCAT to provide the comma-delimited list of subcategories
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •