php - mySQL query for selecting children

Question

I am not sure if this is possible in mySQL. Here are my tables:-

Categories table:

• id
• name
• parent_id (which points to Categories.id)

I use the above table to map all the categories and sub-categories.

Products table:

• id
• name
• category_id

The category_id in the Products table points to the sub-category id in which it belongs.

e.g. If I have Toys > Educational > ABC where ABC is product, Toys is Category and Educational is sub Category, then ABC will have category_id as 2.

Now the problem is that I want to use a SQL query to display all the products (in all the sub-categories and their sub-categories.. n level) for a particular category.

e.g.:

select * from categories,products where  category.name = 'Toys' and ....

The above query should display the products from Educational also and all other sub categories and their subcategories.

Is this possible using a mySQL query? If not what options do I have? I would like to avoid PHP recursion.

Update: Basically I want to display the top 10 products in the main category which I will be doing by adding a hits column to products table.

Answer 1

What I've done in previous projects where I've needed to do the same thing, I added two new columns.

• i_depth: int value of how deep the category is
• nvc_breadcrumb: complete path of the category in a breadcrumb type of format

And then I added a trigger to the table that houses the category information to do the following (all three updates are in the same trigger)...

-- Reset all branches
UPDATE t_org_branches
    SET nvc_breadcrumb = NULL,
    i_depth = NULL

-- Update the root branches first
UPDATE t_org_branches 
    SET nvc_breadcrumb = '/', 
        i_depth = 0 
    WHERE guid_branch_parent_id IS NULL

-- Update the child branches on a loop
WHILE EXISTS (SELECT * FROM t_branches WHERE i_depth IS NULL) 
    UPDATE tobA 
        SET tobA.i_depth = tobB.i_depth + 1, 
            tobA.nvc_breadcrumb = tobB.nvc_breadcrumb + Ltrim(tobA.guid_branch_parent_id) + '/' 
        FROM t_org_branches AS tobA
            INNER JOIN t_org_branches AS tobB ON (tobA.guid_branch_parent_id = tobB.guid_branch_id) 
        WHERE tobB.i_depth >= 0 
            AND tobB.nvc_breadcrumb IS NOT NULL 
            AND tobA.i_depth IS NULL

And then just do a join with your products table on the category ID and do a "LIKE '%/[CATEGORYID]/%' ". Keep in mind that this was done in MS SQL, but it should be easy enough to translate into a MySQL version.

It might just be compatible enough for a cut and paste (after table and column name change).

---

Expansion of explanation...

t_categories (as it stands now)...

Cat Parent  CategoryName
1   NULL    MyStore
2   1       Electronics
3   1       Clothing
4   1       Books
5   2       Televisions
6   2       Stereos
7   5       Plasma
8   5       LCD

t_categories (after modification)...

Cat  Parent  CategoryName   Depth   Breadcrumb
1   NULL    MyStore         NULL    NULL    
2   1       Electronics     NULL    NULL
3   1       Clothing        NULL    NULL
4   1       Books           NULL    NULL
5   2       Televisions     NULL    NULL
6   2       Stereos         NULL    NULL
7   5       Plasma          NULL    NULL
8   5       LCD             NULL    NULL

t_categories (after use of the script I gave)

Cat  Parent  CategoryName   Depth   Breadcrumb
1   NULL    MyStore         0       /   
2   1       Electronics     1       /1/
3   1       Clothing        1       /1/
4   1       Books           1       /1/
5   2       Televisions     2       /1/2/
6   2       Stereos         2       /1/2/
7   5       LCD             3       /1/2/5/
8   7       Samsung         4       /1/2/5/7/

t_products (as you have it now, no modifications)...

ID   Cat Name
1   8   Samsung LNT5271F
2   7   LCD TV mount, up to 36"
3   7   LCD TV mount, up to 52"
4   5   HDMI Cable, 6ft

Join categories and products (where categories is C, products is P)

C.Cat Parent CategoryName   Depth   Breadcrumb  ID   p.Cat  Name
1    NULL   MyStore         0       /           NULL NULL   NULL
2    1      Electronics     1       /1/         NULL NULL   NULL
3    1      Clothing        1       /1/         NULL NULL   NULL
4    1      Books           1       /1/         NULL NULL   NULL
5    2      Televisions     2       /1/2/       4    5      HDMI Cable, 6ft
6    2      Stereos         2       /1/2/       NULL NULL   NULL
7    5      LCD             3       /1/2/5/     2    7      LCD TV mount, up to 36"
7    5      LCD             3       /1/2/5/     3    7      LCD TV mount, up to 52"
8    7      Samsung         4       /1/2/5/7/   1    8      Samsung LNT5271F

Now assuming that the products table was more complete so that there is stuff in each category and no NULLs, you could do a "Breadcrumb LIKE '%/5/%'" to get the last three items of the last table I provided. Notice that it includes the direct items and children of the category (like the Samsung tv). If you want ONLY the specific category items, just do a "c.cat = 5".