NYCPHP Meetup

[DeWitt, Michael]

How about getting counts of articles for the parent and then you can walk
the counts checking to see if articles exist?

step 1
		get parent id of category needed (select from categories)

step 2 
		get counts for all categories under parent id ( join
articles to categories ) 

step 3
		if you have article(s) under the category id fetch them
		else show tree of categories within parent that do have
counts

This method should allow you to walk up and down the parent/category tree. I
know you mentioned walng down from the parent to category, but what if they
said "apples" and you have no article, what will you offer instead?


Mike

> -----Original Message-----
> From:	Dan Cech [SMTP:dcech at phpwerx.net]
> Sent:	Friday, March 11, 2005 4:43 PM
> To:	NYPHP Talk
> Subject:	Re: [nycphp-talk] Q: How can I get all of the articles under
> a certain category?
> 
> 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
> 
> _______________________________________________
> New York PHP Talk Mailing List
> AMP Technology
> Supporting Apache, MySQL and PHP
> http://lists.nyphp.org/mailman/listinfo/talk
> http://www.nyphp.org