NYCPHP Meetup

[Dan Cech]

John,

On a side note, if you are forced to build the recursive system it can 
actually be more efficient to retrieve the entire hierarchy tree with 
one SQL call and build it up in memory that to execute a query for each 
category.

Dan

John Nunez wrote:
> 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