Multiple Category Help

[Tapan]

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:

$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.

[gdtrfb]

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

[Tapan]

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.