PHP/MySql incrementing multiple ID's from a table row

[quasiman]

I've been banging my head on the desk long enough...hope someone can help me on this!

 

I have a db table of products:

SKU | SectionCat | ClassCat | FamilyCat

 

And I'm trying to insert the categories into my shop db tables:

 

category:

category_id | category_name

 

category cross-reference:

category_parent_id | category_child_id

 

Now...I'm not asking anyone to write code for me, I just need a pointer to correctly increment each Section, then Class, then Family.  The trouble I'm having is getting past Section. 

If I do a query like this "SELECT DISTINCT SectionCat,ClassCat,FamilyCat FROM products", I'll get distinct sections, but my increment won't work because it's incrementing on duplicate classes and families.

 

ugh...please help

[quasiman]

Err....reverse that:

I'll get distinct families, but my increment won't work because it's incrementing on duplicate sections and classes.

 

[quasiman]

I hate bumping, but....I really do need some help on this one

[quasiman]

Here's what I have so far...for each group it works, but I need to also have the parent ID flow through, like this:

<h3>Sections</h3>

Tools - 1

Light - 2

Knives - 3

etc....

<h3>Classes</h3>

1 - Tools - Gerber Tools - 22

2 - Light - Gerber Lighting - 23

3 - Knives - Gerber Knives - 24

3 - Knives - Gerber Tools - 25

3 - Knives - Buck Knives - 26

etc...

and the code:

 

<?php
$host = "localhost";
$user = "root";
$pass = "";
$database = "database";
$linkID = mysql_connect($host, $user, $pass) or die("Could not connect to host.");
mysql_select_db($database, $linkID) or die("Could not find database.");
$result = mysql_query("SELECT DISTINCT `SectionCode` FROM `dbprodfile`") or die(mysql_error());
$result2 = mysql_query("SELECT DISTINCT `SectionCode`,`ClassCode` FROM `dbprodfile`") or die(mysql_error());
$result3 = mysql_query("SELECT DISTINCT `SectionCode`,`ClassCode`, `Family` FROM `dbprodfile`") or die(mysql_error());
echo "<h3>Sections</h3>";
$sid = 0;
while($row = mysql_fetch_array( $result )) {
$sid++;
$var = ;
echo  convertcase($row['SectionCode']). " - " . $sid . "\n<br>";
}
echo "<h3>Class</h3>";
$cid = $sid;
while($row = mysql_fetch_array( $result2 )) {
$cid++;
echo convertcase($row['SectionCode']) . " - " . getclass($row['ClassCode']) . " - " . $cid . "\n<br>";
}
echo "<h3>Family</h3>";
$fid = $cid;
while($row = mysql_fetch_array( $result3 )) {
$fid++;
echo convertcase($row['SectionCode']) . " - " . $row['ClassCode'] . " - " . $row['Family'] . " - " . $fid . "\n<br>";
}

function convertcase($value) {
//$catdesc = ucwords(strtolower($row['CatDesc']));
//converts "THIS" to "This"
$new = ucwords(strtolower($value));
return $new;
}

function secure($value) {
$new = mysql_real_escape_string($value);
return $new;
}
function getclass($value) {
$query = "SELECT `ClassCode`,`ClassShort`,`ClassLong` FROM `dbprodfile`,`dbclassfile` WHERE ClassShort = '".$value."'";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array( $result )) {
if (mysql_num_rows($result) > 0) {
return $row['ClassLong'];
} else { 
	return $row['ClassShort'];
}
}	
}
?>

[quasiman]

Nevermind, I did it.  I finally just created 3 separate tables, for:

sections

sections & classes

sections & classes & family. 

 

From there I was able to write join queries to get each name and their ID's.  Not exactly ideal, but it worked...