Search in all child categories

[Anon]

I have a table in a database which stores categories and their description. categories can be under each other to form subcategories. So, the table has 3 fields Catname, catDesc and Cat_ID. If Cat_ID is 0, then it is a root category. Now i need my users to be able to search for text in the descipeiotns. If a suer searchs for 'hotel', it shuold be searched in say, cat1...and ALL categories udner it. Hwo do I form the query?

table e.g.
ID, Catname, catDesc, Cat_ID
1, outdoor, outdoor games, 0
2, indoor, indoor games, 0
3, cricket, cricket, 1
4, chess, chess, 2
5, batsmen, batsmen, 3

Thank you.

[[deleted]]

select * from table where (ID = 1 OR Cat_ID = 1) AND catDesc LIKE "%hotel%"

Give that a try....

Matt Wade
codewalkers.com

[Anon]

Umm, i dont think this will work for my problem. See the tree can be as deep as it wants...not just twolevels deep.

e.g.

Event > sports > outdoor > cricket > batsmen > bat

or whatever.

So if i search in sports, it shuold search in all categories below it.

if you wish, you can suggest a better database design too.