NYCPHP Meetup

[John Nunez]

I want to thanks everyone for their comments!  The client and my boss 
decided to drop this feature.  Notch one for the developer.

I want to learn more about Nested Set Models so I have been googling 
around on this. This project would take a lot of work to convert (270 
category entries, 57 on the root). It was the fact that 3000 articles 
would be displayed on the click of Root that made the client change 
their minds. Their board members are still on AOL Dial Up.

Thanks,
John


On Mar 11, 2005, at 4:57 PM, Hans Zaunere wrote:

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