NYCPHP Meetup

[John Nunez]

Hi Guys,

How can I get all of the articles under a certain category?

I have a table with the following structure:

Categories
ID - int
CategoryName - varchar(128)
ParentID - int [Foreign Key to Categories->ID]

Articles
ID - int
Category_ID - int [Foreign Key to Categories->ID]
Article_Name - varchar(128)

Sample Category Tree
--------------------
Food[1]
--Natural[2]
----Fruits[3]
------Apples[4]
------Pears[5]
------Oranges[6]

Now the client wants to be able to click on "Natural" and if there are 
no articles listed directly under it get all the articles for "Fruits", 
"Apples", "Pears", "Oranges".

The only solution I have is to write a recursive function that will 
retrieve each CategoryID that has the ParentID of the argument passed. 
Once I have this array of CategoryIDs can then run a query on the 
Articles table.  As this tree grows it can amount to tons of MySQL 
Queries for each click that might be skipped anyway. Is there anyway to 
do this with one or two calls in MySQL?

I have argued against this but "the powers that be" told me it's a must 
have feature.

Thanks,
John