related tables with many levels

Im trying make a menu with the next structure:

-category 1
--category 1.1
---category 1.1.1
----product 1
----product 2
----produc开发者_JAVA技巧t 3
-category 2
--category 2.1
---category 2.1.1
----product 1
----product 2
----product 3
-category 3
--category 3.1
---category 3.1.1
----product 1

I have an scheme like wordpress:

relation_category_products table store an id from category and id from products. The question is whats the best way to make the mysql queries for having all that structure? My first solution it was make queries for parent category (-category), then for each every row take the id for the next --category and then until raise the product node. But with that tecnique theres a lot of queries (35 for the moment). And i dont know whats the better way to get all that relationship and take it with php for render the menu.

thanks

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

Your table structure should have a field for the parent, e.g.

Table "category"

id(int)  name            parent(int)
   1     Category 1 
   2     Category 1.1     1
   3     Category 1.1.1   2
   4     Category 2       
   5     Category 2.1     4

You then use a one-to-many relationship in your "Product" table to link them to a category.

If you want to retrieve the nodes directly under a category, just SELECT by the "parent" field.

If you want to retrieve the whole tree use a join query:

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.id
LEFT JOIN category AS t3 ON t3.parent = t2.id

This will give you back something like:

+-------------+----------------------+---------------+
| lev1        | lev2                 | lev3          |
+-------------+----------------------+---------------+
| Category 1  | Category 1.1         | Category 1.1.1|
| Category 2  | Category 2.1         | NULL          |
+-------------+----------------------+---------------+

You could use a leftjoin and then loop the array with a foreach make sure you croup them correctly and use the primary column alt. an indexed one for best performance there is a good guide for that here

regards