multi category with mysql

[saeed_violinist]

Dear Friends,

 

I working on a simple online shop, My problem is I dont know the table structure needed for having a multi category shop system. I looked in some opensource shops and I saw they use 2 table for making multi category. but I dont know how to connect this tables with eachother.

 

My Categories might be as follow:

 

|Software

|_Computer Games

|___ Stategy

|___ Adventure

|___ Action

|___ FPS

|_Movies

|___ Action

|___ Thriller

|___Commedy

|

|Hardware

|_Mainboard

|___ ASUS

|___ ABIT

|_Hard Disk

|_PSU

|_VGA

 

I need some help and suggestion about this, and any link to an article related to this will be appriciated!

 

Thanks in advance.

 

 

[Vikas Jayna]

You can use a two table structure wherein there is one table is used to store categories i.e. the table will contain exactly one record for each category. The second table will then be used to represent a parent-child relationship between the categories. For e.g. in the above case "software" is a parent of "computer games" and "computer games" is a parent of "strategy".

[rcorlew]

I would use a schema like this:

 

Software - ref id = 1 (in it's own table as Categories)

Computer Games - ref id = 100

Strategy - ref id = 1000

 

Hardware - ref id = 2 (in it's own table as Categories)

Mainboard - ref id = 200

Asus - ref id =2000

 

The reason a schema like this is important:

 

<ol>

<li>Makes maintaining your database easier</li>

<li>You can trim off 0's or add 0's to get up or down the list</li>

<li>The ref id cross referencing would make much more sense</li>

<li>This schema utilizes good database normalization methods</li>

<li>You would only have to do a simple "join" to link the two tables together using a coder friendly db design</li>

</ol>

 

I think you can get the picture with that.

[Wildbug]

I would use a schema like this:

 

Software - ref id = 1 (in it's own table as Categories)

Computer Games - ref id = 100

Strategy - ref id = 1000

 

Hardware - ref id = 2 (in it's own table as Categories)

Mainboard - ref id = 200

Asus - ref id =2000

 

Doesn't that schema only support 10 top-level categories (or 36 using alpha+numeric)?  I guess you could use multiple-length digits:  01, 0101, 010101.

[rcorlew]

Yeah, I was just giving some advice that they could expand on based on the information that was posted.

 

I was trying to show how to relate two or more tables based on a common tie that would make sense to someone tackling a new problem.

[saeed_violinist]

Thanks, I got it somehow. but how do I connect two tables with eachother in queries? can you give me an example? I think It must be done with GROUP BY aggrigate am I right?

 

and is there an article related this parent-child schema out there in web ?

 

 

[rcorlew]

Here is one idea that might help you. Let's say you are choosing software which has a category ref of 1, then you have operating systems which has a ref id(referring id) of 10, then you go down farther on the list to Linux which could have a ref id of 100, then say red hat linux could be 1000 then you would get to the separate distributions would all begin with 1001 and go to 1999.

 

To run queries to do category breakdowns you could trim and pad the "1" or whatever the main category ref id is.

 

It would look something similar to this:

 

<?php

$main_id = "$_GET["main_id"]";
$cat1 = str_pad($main_id, 1, "0"); // <-- adds one zero to the right side giving you level two of your table
$cat2 = str_pad($main_id, 2, "0"); // makes level three

//Then your query could be something like this to get to level three

$query = "SELECT * FROM products_table WHERE ref_id > '$cat2' AND ref_id < ($cat2 + 1000)";

?>

 

That should get you pointed in the right direction, I have not made an eshop before, but I would assume it would be very similar to that.

[saeed_violinist]

Thanks friend, its a bright idea, I will try it.

 

anyone knows an article about parent-child with php & mysql?

 

[fenway]

There are many good dev articles on mysql.com.

[Wildbug]

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

 

I think fenway's referring to that.