SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Enthusiast Fizzman's Avatar
    Join Date
    May 2003
    Location
    Australia
    Posts
    84
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Arrow Categories / Subcategories

    Hi everyone,

    I'm programming a directory-type thingy, and would love a bit of help.

    I want to be able to add categories, with unlimited subcategories, and each of those subcategories will have it's own subcategories and so on. (Unlimited Levels)

    I have the table categories:

    id parent name

    1 NULL 2002 (top level)
    2 1 toyota
    3 1 nissan
    4 2 supra
    5 NULL 2003 (top level)
    6 4 toyota
    7 4 nissan
    8 5 supra
    9 5 celica

    So it looks like this:

    - 2002
    - nissan
    - toyota
    - supra
    -2003
    - nissan
    - toyota
    - supra
    - celica

    okay, now the URL will be something like:

    /2003/toyota/supra - I'll use pathinfo and explode '/' to get the last directory, which in this case is 'supra'. My question is, how would I match this 'supra' to the id 8, and not 4.

    If I select where name = 'supra', I will get 2 results. but I'm only after the one which is under the path /2003/toyota/supra.

    Am I making any sense?

    Anyone have any suggestions?

    Thanks in advance!
    Last edited by Fizzman; Oct 11, 2003 at 00:13.

  2. #2
    does not play well with others frezno's Avatar
    Join Date
    Jan 2003
    Location
    Munich, Germany
    Posts
    1,391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    all this isn't as trivial as it might look like.

    Basically it's all a matter of (the right) database query. But you have to bear in mind:

    e.g. what happens with the items of one (sub)category (and the lower categories) if a higher category of the same branch gets deleted?
    My question is, how would I match this 'supra' to the id 8, and not 4.
    each car has it's unique id (e.g. with auto_increment), car_id.

    Next you need another table:
    tbl_cars_categories which contains just the car_id and the appropriate category_id of that car.

    Now you can get the right category of that car with just a query.
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.

  3. #3
    SitePoint Guru
    Join Date
    Feb 2002
    Location
    NZ
    Posts
    620
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  4. #4
    SitePoint Enthusiast Fizzman's Avatar
    Join Date
    May 2003
    Location
    Australia
    Posts
    84
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by frezno
    each car has it's unique id (e.g. with auto_increment), car_id.

    Next you need another table:
    tbl_cars_categories which contains just the car_id and the appropriate category_id of that car.

    Now you can get the right category of that car with just a query.
    Sorry, I probably didnt explain it very well.

    'supra' is its own category, and not a item under a category.



    I really appreciate your help.

    I got it working with this piece of code:

    PHP Code:
    $cats explode("/",$PATH_INFO);

    $i 0;

    while (
    $i <= (count($cats) - 1)) {
        if(
    $i == 0) {
            
    $and 'parent is NULL';
        } else {
            
    $and 'parent = ' $result['id'];
        }
        
    $result $db->GetRow("SELECT id FROM testcat WHERE name = '" $cats[$i] . "' AND $and");
        if (!
    count($result)) {
            echo 
    'That category does not exist.';
            break;
        }
        if(
    $i == (count($cats) - 1)) {
            
    $final_result $result['id'];
        }
        
    $i++;

    The only problem here is that it is recursive and and executes alot of queries.

    BTW, Thanks for the link Motivated.
    Last edited by Fizzman; Oct 11, 2003 at 03:35.

  5. #5
    does not play well with others frezno's Avatar
    Join Date
    Jan 2003
    Location
    Munich, Germany
    Posts
    1,391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well, i still have not really an idea how you handle all this.
    Somehow you get to the page e.g. .../2003/toyota/supra and now you want to list all the items (cars) which belong to this category?

    At least, i think it's all about the same as i'm doing it in my shop:
    There are unlimited categories with unlimited subcategories with unlimited items.
    The querie(s) to get a (sub)categorie and the items is pretty simple and not really time consuming.
    Maybe there must be modified something how you've built all this?
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.

  6. #6
    SitePoint Enthusiast Fizzman's Avatar
    Join Date
    May 2003
    Location
    Australia
    Posts
    84
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Say the URL is http://www.domain.com/2003/toyota/supra
    PHP Code:
    $cats explode("/",$PATH_INFO); 

    $i 0

    while (
    $i <= (count($cats) - 1)) { 
        if(
    $i == 0) { 
            
    $and 'parent is NULL'
        } else { 
            
    $and 'parent = ' $result['id']; 
        } 
        
    $result $db->GetRow("SELECT id FROM testcat WHERE name = '" $cats[$i] . "' AND $and); 
        if (!
    count($result)) { 
            echo 
    'That category does not exist.'
            break; 
        } 
        if(
    $i == (count($cats) - 1)) { 
            
    //this will return the id of the requested category
            
    $final_result $result['id']; 
        } 
        
    $i++; 

    That's the code I posted above. This will return the catid of the subcategory 'supra'. That's all that I want - the catid of the requested subcategory. The reason I can't do a simple "select id from testcat where name= 'supra'", is because there might be another category named supra (at a different level or in a different category).

    Then I will use the retreived ID to do a lookup for items in that category. (Which isn't really what I needed help with - just the above).

    PHP Code:
    select from entries where catid $final_result 
    Does that make sense?

  7. #7
    PHP manual bot bronze trophy Gaheris's Avatar
    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,195
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try looking up 'Nested Sets', they could help.

  8. #8
    SitePoint Enthusiast Fizzman's Avatar
    Join Date
    May 2003
    Location
    Australia
    Posts
    84
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Gaheris
    Try looking up 'Nested Sets', they could help.
    Hi Gaheris,

    Thanks for your suggestion! The only problem with Nested Sets is that it really is a pain to move/edit/delete/add categories.

    Regards

  9. #9
    PHP manual bot bronze trophy Gaheris's Avatar
    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,195
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep, you're right of course. But in this case getting the (sub-)categories very fast is a top priority, isn't it?

  10. #10
    SitePoint Enthusiast Fizzman's Avatar
    Join Date
    May 2003
    Location
    Australia
    Posts
    84
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, but there will probably be thousands of categories and it's going to become very hard with to manage with nested sets. I'm sort of looking for an in-between solution (if there is any).

    Hmm, what about caching all the queries? Would that help reduce the db load?

  11. #11
    SitePoint Enthusiast Fizzman's Avatar
    Join Date
    May 2003
    Location
    Australia
    Posts
    84
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Heres a good example:

    http://www.hotscripts.com/PHP/Script...ams/Calendars/

    http://www.hotscripts.com/ASP/Script...nts/Calendars/

    The last subcategory in both cases is called 'Calendars'. Just wondering how they map the URL, to a catid.

    Anyone?

    Thanks!

  12. #12
    SitePoint Zealot prefab's Avatar
    Join Date
    Jan 2003
    Location
    Belgium
    Posts
    133
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you need a managable approach to nested sets take a look at these:

    http://oss.webcluster.at/

    or:

    http://www.phpkitchen.com/article.ph...30427152620585

    Both are available through PEAR.

    Good luck!

    - prefab

  13. #13
    SitePoint Addict shad0w's Avatar
    Join Date
    Aug 2003
    Location
    PA
    Posts
    239
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try a simple table like this:

    id | category | parent category

    Then you know the top level categories are top level because they wont have a parent category, you can have an infinite amount of subcategories this way.

  14. #14
    SitePoint Evangelist GeekSupport's Avatar
    Join Date
    May 2002
    Location
    Southern California
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i had a problem similar to this and ended up using a method similar to what shad0w said

    table
    id | name | parent_id | display_rank | indent_level

    you would have to use recursion if you want to show the full/partial list such as the dropdown box in this forum (Forum Jump: menu)

    if you plan to use this method here are some reference links

    http://www.evolt.org/article/Four_wa...l_data/17/4047
    http://searchdatabase.techtarget.com...537290,00.html


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
  •