creating category structure php mysql

[robpicken]

Hi all,

I've spent the past couple of days trying to sort out the category structure for a new web site i'm building. First of all i was informed it was going to be just 2 levels deep, now its looking as though it could be 3 or 4 levels deep for some cats.

The part i'm really struglling with is getting the 3 level deep cats to show up properly in a drop down menu, as i need it to form part of an add product form.

I can do the 2 levels, and that works, and i've tried to create a work around to get the three levels working. So far if i get the three levels showing up in the dd menu, i only get those showing up and no other cats, with a mysql error message for each three level cats that are showing up, calling up an mysql error in the query, (hope you follow me).

I've also tried running the code from a category tree table, and the normal cats table, but just can't quite fix it.

I've included the code below, its kind of a mess as i've spent most of the past 3 hours trying to fix it and ended up confusing myself, so if anybody can point me in the right direction or even pass on advice as to the best way of doing this bit i'd be most grateful.

[code:1:e55fd47981]$sql = mysql_query("SELECT * FROM brochure_cats");
while($row = mysql_fetch_array($sql)) {
$id = $row['cat_id'];
$parent = $row['parent_id'];
$name = $row['cat_name'];

// Lets Get its Parent
$parent = mysql_query("SELECT * FROM brochure_cats where cat_id='$parent'");
while($row = mysql_fetch_array($parent)) {
$parent_id = $row['cat_name'];
$parent_name = $row['parent_id'];

$middle = mysql_query("SELECT * FROM brochure_cats WHERE cat_id='$parent_name'");
while($row = mysql_fetch_array($middle)) {
$middle_id = $row['cat_name'];

$selector = ' :';
$parent_output = $parent_id . $selector;
$output .= "<OPTION VALUE="$id">$middle $parent_output $name</OPTION> \n";

}
}
return $output;

}[/code:1:e55fd47981]

oh, p.s, the parent and middle bits pull up the info the wrong way round as you'll notice in the output element.

Many Thanks,

[littleFella]

show us the data structure as it presents itself withing the database tables, along with relations between tables.

[robpicken]

i'm guessing you want the table set up, for the tables involved ie the product table and the cats table?

The cats table:

cat_id (autoincrement) primary
parent_id varchar index
cat_name varchar
description text

The Products Table

product_id (autoincrement) primary
name varchar
description text
cats varchar index

I've also got the cat tree table which i tried to use:

cat_id primary
parents index
children

Hopefully thats what you were after, if there are any errors or strange things with the tables, please don't ask me why i did them cause i prob won't know

Thanks

[littleFella]

That setup seems a little suspicious. I may not fully understand what kind of realtions you work with but the cursory look at your tables would suggest that you need to study relational databases and normalization a little more.

Anyway, you need 4 levels, so Iwould suggest the following tables (using an imaginary product):

TABLE_PPARENT
PARENT_ID (PK) // level 1
PARENT_DESCR

TABLE_CAT
CAT_ID (PK) // level 2
PARENT_ID (INDEX)
CAT_DESCR

TABLE_PRODUCT // level 3
PRODUCT_ID (PK)
CAT_ID (INDEX)
PRODUCT_DESCR

TABLE_COLOR // level 4
COLOR_ID (PK)
PRODUCT_ID (INDEX)
COLOR_NAME_DESCR

Takea careful look at these tables and notice how there is little redundant data and yet you can associate any item from any table with any other item frm any other table based on the relations among the 4 tables

Of course this would be done with a true RDBMS, which most of MySQL versions are not. I'm not sure how well MySQL implement SQL-92, which is critical to creating complex queries. If it's good enough then you're into some heavy duty SQL exercises, or you may want to opt for a less elegant, but much simpler solution. This would equire you to exectute a series of queries (instead of one) to get all the data you need. Sometimes, it wouldnt be such a bad idea anyway, especially if the number of records is substantial.

[robpicken]

thanks for the info,

i did loads of searches and most of the decent info i could find reflected what you said about MYSQL and not been able to certain things. I think most of the info i found said that the best way was based around nodes.

My initial feeling when i read that was that it wasn't really worth dealing with nodes as i only needed 3 cat levels.

I actually spent a couple of hours rebuilding the queries like you suggested and have now got it to show the two and three level deep cats in the same drop down menu. Its still not 100% perfect, but am working on it.

Thanks for the help though, you highlighted some good points