SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    563
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Multiple Category Help

    Hi,

    I made a simple script in which i have categories etc. Its a multi category system in which user can go to any depth and for that my table structure is following:

    Table: categories
    id (Auto Number)
    pid (medium int)
    title (varchar 25)

    I am using mod_rewrite to generate urls and displaying on web page. Please have a look at the following examples:

    Category:
    Computers/Internet
    Games/Internet

    So these are 2 different categories in 2 different parent categories. Now when someone click on the link it takes them to following respectively:

    http://www.somedom.com/Computers/Internet
    http://www.somedom.com/Games/Internet

    and a show-category.php file is executed so it can display the list of websites in that category.

    Now my question is how do i fetch the correct category using the URL above ? I use the following to fetch the url and split the cats/sub cats into an array:

    PHP Code:
    $category explode ("/"substr($_SERVER['REQUEST_URI'], 1)); 
    Now how do i proceed further so i get the correct category id from the data of the particular id. If i try to do a simple lookup ion the db using the SELECT sql statement then the problem is that i end up getting wrong category.

    For example if user has selected Games/Internet but the Computers/Internet comes up first...so if i make a query like: SELECT * FROM categories WHERE title = 'Internet' then i will get the first record that is of Internet.

    I hope i have described my problem. Please help me.

    Thanks.

  2. #2
    SitePoint Zealot
    Join Date
    Sep 2004
    Location
    Boston
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, if you were just allowing two levels of categories, as in the example you provide, you do a sql query like this:

    [sql]
    select p1.id, p1.title from categories as p1, categories as p2 where p1.title = 'Internet' and p2.id = p1.pid and p1.title = 'Games';
    [/sql]

    That would give you the correct categoryID for Games/Internet.

    But, since you said users can go to any depth for categories, you'll need to start at the left and work your way across.

    Start with games, get all all the categories that have games as a parent. Find your match in those for Internet. Then (if this were a deeper example) find all the categories that have that particular internet category as a parent and so on. There isn't a neat one-query sql way to do that when your path can be an arbitrary depth.

    Although I suppose you could also look at the very last category in the string and do a quick query on that one. If you get one and only one match then you know you've found it.

    YMMV,
    --Mark

  3. #3
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    563
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Thanks for the reply. I am already doing it using the last category match thing but that has started giving wrong results as when i try to search for Internet in the db ...i get the Internet which is under Computers and not under Games so this is really causing the probelm.

    I think i have to do it by left method and then verfying if i am on right track or not.

    Thanks.


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
  •