NYCPHP Meetup

[Dan Cech]

John,

Unfortunately there is no easy way that I know of to do this with the 
structure you are using, because it's not a 'true' hierarchy.

My favorite solution is a modified version of Joe Celko's Nested Sets.

Basically there is no elegant way to do it without changing your 
database structure.

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
> 
> _______________________________________________
> New York PHP Talk Mailing List
> AMP Technology
> Supporting Apache, MySQL and PHP
> http://lists.nyphp.org/mailman/listinfo/talk
> http://www.nyphp.org