12-08-2007, 05:51 PM
|
#4 (permalink)
|
|
The Acquainted
Join Date: Nov 2007
Posts: 127
Thanks: 14
|
The modified preorder tree traversal methods supports as many children as you need per parent. I've already implemented it with 10+ children on certain parents.
PHP Code:
$connection = mysql_connect("localhost", "root", "x");
$database = mysql_select_db("test", $connection);
$result = mysql_query("SELECT COUNT(parent.category_id) AS depth, cat.name as cname, products.name as pname
FROM categories AS parent
INNER
JOIN categories AS cat
ON cat.lft BETWEEN parent.lft AND parent.rgt
LEFT OUTER
JOIN products
ON products.category_id = cat.category_id") or die(mysql_error());
$current_cat = NULL;
while($row = mysql_fetch_assoc($result))
{
if($current_cat != $row['cname'])
{
/*display the new category however you wish here*/
echo str_repeat(" ", $row['depth']) . "<strong>" . ucwords(strtolower($row['cname'])) . "</strong><br />\n";
$current_cat = $row['cname'];
}
/*display the product however you wish here;
you could use $row['depth'] to line up with the current (sub-)category*/
echo str_repeat(" ", $row['depth']) . $row['pname'] . "<br />\n";
}
Here is some not so good code which I used as a base to link items to categories.
My table structures were:
sql Code:
CREATE TABLE categories (
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
lft INT NOT NULL,
rgt INT NOT NULL
);
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(40),
category_id INT NOT NULL
);
And some sample data:
Code:
"category_id","name","lft","rgt"
1,"ELECTRONICS",1,20
2,"TELEVISIONS",2,9
3,"TUBE",3,4
4,"LCD",5,6
5,"PLASMA",7,8
6,"PORTABLE ELECTRONICS",10,19
7,"MP3 PLAYERS",11,14
8,"FLASH",12,13
9,"CD PLAYERS",15,16
10,"2 WAY RADIOS",17,18
"product_id","name","category_id"
1,"product under television",2
2,"product under television",2
3,"product under tube",3
4,"product under lcd",4
5,"product under lcd",4
6,"product under plasma",5
7,"product under flash",8
8,"product under portable electronics",6
9,"product under portable electronics",6
10,"product under portable electronics",6
11,"product under television",2
12,"product under electronic",1
13,"product under cd players",9
Once you get a firm grasp on how the left/right values work. The amount of flexibility is indeterminable. And as you can see, there is no recursion. We query our database once and use very simple logic. |
|
|
|