1

First of all, I couldn't find a better name for the topic's title, if you can, please edit the title.

I got the following tables:

products
- id
- name
- desc
- image

categories
- id
- name
- parent_id

products2categories
- id
- product_id
- category_id

Let's say that I got this information in the tables:

products
- 102
- Pilot Pen Blue
- Some pen desc blah blah blah
- Some image location

categories
- 10           -11
- Pens         -Pilot Pens
- None         -10

products2categories
- 1
- 102
- 11 -> ofcourse if it's related to category 11, it also related to category 10

Looking at the demo information, we can see that we got "Pilot Pen Blue" which is related to category number 11 (Pilot Pens), but since "Pilot Pens" is a "child" of "Pens" Whenever the user will look for ALL pens available, I would like to show the Pilot pen aswell.

How do I make that happen?

If you guys have any other good database structure for Catalog, please feel free to suggest!

kfirba
  • 5,231
  • 14
  • 41
  • 70

2 Answers2

0

With Oracle (I know it is not your system, it is just for the info), you are able to make some "hierarchical queries" with "CONNECT BY/PRIOR" (the category table with a father/child relationship is the classic case).

I found an other topic that speaks about that : Connect By Prior Equivalent for MySQL

They talk about a blog article called "Hierarchical queries in MySQL" blog that demonstrates how can you can have similar results in MySql.

Community
  • 1
  • 1
d34n5
  • 1,308
  • 10
  • 18
  • I've forgot to mention that im talking about implementing this for website usage using PHP. Therefore, I'm not sure that the method shown is goof for me. – kfirba Aug 05 '13 at 13:58
  • Actually you can make hierarchical (recursive) queries nearly all modern DBMS - just not MySQL –  Aug 05 '13 at 16:28
0

You need to select parent-child categories in the same table. the problem of a simple select solution with MySql is that your query will be dependant of the number of level/depth you have. If there is only one level parent-child relationship, the query proposed by otisonoza is fine, but as you asked him, what "if the depth of the category is unknown"? what if you have a 4 level depth?

There is several solutions but each of them will construct your query, depending of the number of levels you have. In some of this solutions, you need to store the level/depth of each of the categories.

The link I proposed earlier gives a solution to fake "CONNECT BY/PRIOR" with a MySQL stored procedure. It will allow you to select hierarchical rows linked by a father/child relationship whatever the depth is.

An other solution is to construct the query string in PHP with a recursive function. There is an interesting article about it that gives you the solution to construct query string for this type of hierarchical data : http://www.sitepoint.com/hierarchical-data-database/

Look especially to the get_path($node) recursive function.

d34n5
  • 1,308
  • 10
  • 18