SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast jameso's Avatar
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Recursive function to print out category hierarchy

    Hi everyone.

    I've got a mysql table called categories, with a few fields: as shown in the create statement below:
    Code:
    CREATE TABLE `categories` (
      `catID` int(11) NOT NULL auto_increment,
      `parentID` int(11) default NULL,
      `name` varchar(255) NOT NULL default '',
      PRIMARY KEY  (`catID`)
    )
    It's for a system that needs to support unlimited subcategories. The root categories have a parentID of NULL, and the others have a parentID matching the catID of it's parent category. I want to write a fuction that prints out the entire category structure like this:

    Code:
    Web Design
      CSS
      HTML
    Web Hosting
      Pre Sales
      Domain Names
      cPanel
        cPanel tutorials
      Email
    The only way I can think of doing it is having lots of SELECT queries to find the child categories of each category. Is it possible to do this with one mysql SELECT query, and then manipulate the results in PHP to print an output similar to the one above?

    Thanks in advance!

    James

  2. #2
    Non-Member
    Join Date
    Oct 2004
    Location
    downtown
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well you are lucky aren't you? I had the same bother myself, and since then have come across a class off the web whereby you do one query, the results are sorted by ascending order of ID.

    There after, you can build a tree structure which has some functionality such as to expand to a given level, expand all, collapse, etc. Even creates a breadcrumb path for you

    I'm going to upload the original zip for you, enjoy
    Attached Files Attached Files

  3. #3
    SitePoint Enthusiast jameso's Avatar
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for that

    I'm trying it out now.

    Will let you know how I go.

  4. #4
    SitePoint Evangelist CapitalWebHost's Avatar
    Join Date
    Apr 2003
    Location
    Albany, NY
    Posts
    417
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just what the doctor ordered..lol. Spent all day working on this very thing..

  5. #5
    SitePoint Enthusiast jameso's Avatar
    Join Date
    May 2002
    Location
    Melbourne, Australia
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I actually found this article - it's quite useful and I've changed my database structure because of it - http://www.sitepoint.com/article/hie...-data-database.

    Its much more efficient than just having the parentID field - saves many mysql queries.

  6. #6
    Non-Member
    Join Date
    Oct 2004
    Location
    downtown
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep, though it does have the disadvantage of being a lot slower during updates. There is a thread started by me on this topic btw you might want to read?

    Mkrz also posted his solution which was very convienent and useful

  7. #7
    Resident Java Hater
    Join Date
    Jul 2004
    Location
    Gerodieville Central, UK
    Posts
    446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Personally, I would not use Celko's sets. It becomes very slow when you get lots of records for updating. If you want to design for scaleablity use...

    http://fungus.teststation.com/~jon/t...eeHandling.htm

    It works much better in most cases and you wouldn't have had to change your DB structure as much. I know that the pure DB / SQL geeks out there will say this goes against SQL because there is some redundent data. However with Celko's sets you really need to implement it with the unit of work (DB transaction). This is because if one of the UPDATE queries fails when inserting a node, then you leave your database in a totally inconsistent way. With the other method that I showed, you can stick to your old schema. Because you have an additional path table, you can use this for fast SELECTS. If the path table messes up, it is easy to reconstruct form the main data table.

    The other advantage is my way is easier to implement than Celko's sets.

    If you go down the Celko route, I would make your left / right values for the root node be as wide as possible, i.e. 0, and (2^32)-1. If you make sure each node has a fairly wide left right value you you do not need to do many update queries to shift the left / right values of nodes (This will be very rare).

    There are other ways, if you use Oracle or another DB with good Stored Procs, then look at Tropashko's method if you are any good with Maths

  8. #8
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by MiiJaySung
    There are other ways, if you use Oracle or another DB with good Stored Procs, then look at Tropashko's method if you are any good with Maths
    In Oracle you can use "CONNECT BY" syntax instead. Simplest thing in the world to maintain.
    Jason Sweat ZCE - jsweat_php@yahoo.com
    Book: PHP Patterns
    Good Stuff: SimpleTest PHPUnit FireFox ADOdb YUI
    Detestable (adjective): software that isn't testable.

  9. #9
    Non-Member
    Join Date
    Oct 2004
    Location
    downtown
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think I get the basic idea of what your link proposes, though for example this part is way beyond me

    See the attached image to see what I mean. Never was any good at algabra myself

    Could you post some script we can look at? I'd appreciate it if you could
    Attached Images Attached Images


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
  •