MySQL Forums
Forum List  »  Newbie

Traversing a Category tree, is there an easy way to do this? Need an experts opinion!!!!!
Posted by: Simon .
Date: July 04, 2005 11:21AM

I have two pretty simple tables,

<----- Category ----->
DROP TABLE IF EXISTS `os_product`.`category`;
CREATE TABLE `category`
(
`category_id` int(10) unsigned zerofill NOT NULL auto_increment,
`name` varchar(30) NOT NULL,
`parent_category_id` int(10) unsigned zerofill NOT NULL default 0,
PRIMARY KEY (`category_id`)
)
ENGINE=MyISAM DEFAULT CHARSET=latin1;

AND

<------ Product ------>
DROP TABLE IF EXISTS `os_product`.`product`;
CREATE TABLE `product`
(
`product_id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(30) NOT NULL,
`category_id` int(10) unsigned NOT NULL,
`price` decimal(8,2) NOT NULL default '0.00',
`stock` int(6) unsigned NOT NULL default '0',
`allocated_stock` int(6) unsigned NOT NULL default '0',
`weight` decimal(4,3) NOT NULL default '0',
`manufacturer_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`product_id`),
KEY `manufacturer_id` (`manufacturer_id`),
FULLTEXT allsearch(`name`)
)
ENGINE=MyISAM DEFAULT CHARSET=latin1;

(ignore the manufacturer id as its irrelevant for this question)

Heres some sample data

<!----- Category ----->

DELETE FROM category;
INSERT INTO category
VALUES
('1', 'Antiques &amp; Art', '0'),
('2', 'Automotive', '0'),
. ('80', 'Cars', '2'),
. . ('85', 'BMW', '80'),
. . ('86', 'Ferarri', '80'),
. . ('87', 'Ford', '80'),
. . ('88', 'Peugeot', '80'),
. . ('89', 'Renault', '80'),
. ('81', 'Classic Cars', '2'),
. ('82', 'Motorcycles', '2'),
. ('83', 'Parts &amp; Accessories', '2'),
. ('84', 'Other Vehicles', '2'),
('3', 'Baby', '0'),
('4', 'Books, Comics &amp; Magazines', '0'),
('5', 'Business, Office &amp; Industrial', '0');

(ignore the '.'s just to make it easier to read)

now what i want to be able to do is from a parent category find all the subcategories and all the sub-subcategories all the way down to the end of the tree.

Obviously the length of the tree is not fixed.

this is being used though java-jdbc so i could write a number of cpu intensive recursive algorithms to execute many sql statements to traverse the tree, but obviously this would be quite time consuming, not to mention resource intensive.

Is there a simpler way?

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.