Generating hierarchical categories based on product row

[quasiman]

I need to build a hierarchical product category table with data from another table.

 

The original product info table has a basic structure:

ID | SKU | Category1 | Category2 | Category3

1 | 001 | Apparel | Boots | Work Boots

2 | 002 | Apparel | Shirts | Long Sleeve

 

The categories table this is going into:

ID | Name | Parent_ID

1 | Apparel | 0

2 | Boots | 1

3 | Work Boots | 2

4 | Shirts | 1

5 | Long Sleeve | 4

 

 

I've been trying to use PHP to increment ID's by:

SELECT DISTINCT Category1

SELECT DISTINCT Category1,Category2

SELECT DISTINCT Category1, Category2, Category3

$i='0';
while ($row = mysql_fetch_assoc($result)) {
$i++;
echo $i . " - ". $row['Category1'] . "<br>";
}

One problem is that I can't get the parent ID that was generated in this method.  Another is that Category2 and 3 are not uniquely named at all.

 

So my question is, how do I distinctly select all three categories, and get each parent ID also?

[Bodhi Gump]

For each category column $i in the original table:

- Get the distinct names. If $i isn't the first column, remember the name of each column's parent column at $i-1;

- If $i is not the first column: get the name-id pairs using the parent column names from the previous step;

- Insert the distinct names into the new table, giving parent_id=0 for the first $i, and giving the parent_id of their parent column for other $i.

 

If you have columns of the same name at the same level, you'll get into trouble. Other than that, this algorithm should work.

[quasiman]

Hi Bodhi, thanks for this....but unfortunately I have a lot of categories at the same level with the same name.

[quasiman]

bump...I really need help on this one

[sasa]

use this 3 queries

INSERT INTO categories SELECT DISTINCT '', Category1, 0 FROM product

INSERT INTO categories SELECT '', Category2, id FROM
(SELECT DISTINCT category1, Category2, categories.ID AS id FROM product, categories
WHERE product.Category1=categories.Name AND categories.Parent_ID=0) AS tbl1


INSERT INTO categories SELECT '', Category3, id FROM
(SELECT DISTINCT category1, Category2, Category3, c1.ID AS id FROM product, categories AS c, categories AS c1
WHERE product.Category1=c.Name AND c.Parent_ID=0 AND product.Category2=c1.Name AND c1.Parent_ID=c.ID) AS tbl1

[quasiman]

Sasa, thank you...that is exactly what I was looking for!