NYCPHP Meetup

[Hans Zaunere]

> 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?

If you're using MySQL 4.1 you can use subqueries, which will reduce the number of distinct queries you're hitting the database with.  There are essentially two forms:

derived tables

SELECT * FROM (SELECT * FROM othertable)

and regular:

SELECT * FROM mytable WHERE column1 = (SELECT column1 FROM othertable)

There are also many variations:

http://dev.mysql.com/doc/mysql/en/subqueries.html

At the end of the day, however, with the structure you have in place, you'll be somewhat limited.  NSM (nested set model) is the way to go if possible.  Here's an implementation of it that I did, although it needs to be updated:

http://pcomd.net/pcom.php?pcomname=pnsm

and could be optimized for MySQL 4.1.


---
Hans Zaunere
President, Founder

New York PHP
http://www.nyphp.org

AMP Technology
Supporting Apache, MySQL and PHP