creating a category system and display them guide?

[xcoderx]

friends i wish to create a category system like this for example

 

first_category_name

 

1. subcategory_name                                      sucbategory_description

2. subcategory_name                                      sucbategory_description

3. subcategory_name                                      sucbategory_description

 

second_category_name

 

1. subcategory_name                                      sucbategory_description

2. subcategory_name                                      sucbategory_description

3. subcategory_name                                      sucbategory_description

 

third_category_name

 

1. subcategory_name                                      sucbategory_description

2. subcategory_name                                      sucbategory_description

3. subcategory_name                                      sucbategory_description

 

 

how to create the table and how to display it like this? guide please if you know.

[ignace]

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

http://www.sqlsummit.com/AdjacencyList.htm

[xcoderx]

ok these bit of sql is doing something like i want but not exactly what i am looking for

 

--

-- Table structure for table `nested_category`

--

 

CREATE TABLE IF NOT EXISTS `nested_category` (

  `category_id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(20) NOT NULL,

  `lft` int(11) NOT NULL,

  `rgt` int(11) NOT NULL,

  PRIMARY KEY (`category_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

 

--

-- Dumping data for table `nested_category`

--

 

INSERT INTO `nested_category` (`category_id`, `name`, `lft`, `rgt`) VALUES

(1, 'ELECTRONICS', 1, 20),

(2, 'TELEVISIONS', 2, 9),

(3, 'TUBE', 3, 4),

(4, 'LCD', 5, 6),

(5, 'PLASMA', 7, ,

(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);

 

-- --------------------------------------------------------

 

--

-- Table structure for table `product`

--

 

CREATE TABLE IF NOT EXISTS `product` (

  `product_id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(40) DEFAULT NULL,

  `category_id` int(11) NOT NULL,

  PRIMARY KEY (`product_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

 

--

-- Dumping data for table `product`

--

 

INSERT INTO `product` (`product_id`, `name`, `category_id`) VALUES

(1, '20" TV', 3),

(2, '36" TV', 3),

(3, 'Super-LCD 42"', 4),

(4, 'Ultra-Plasma 62"', 5),

(5, 'Value Plasma 38"', 5),

(6, 'Power-MP3 5gb', 7),

(7, 'Super-Player 1gb', ,

(8, 'Porta CD', 9),

(9, 'CD To go!', 9),

(10, 'Family Talk 360', 10);

 

 

 

to display

 

SELECT parent.name, COUNT(product.name)

FROM nested_category AS node ,

nested_category AS parent,

product

WHERE node.lft BETWEEN parent.lft AND parent.rgt

AND node.category_id = product.category_id

GROUP BY parent.name

ORDER BY node.lft;

 

 

now do to get this to make it something im looking for?

[trq]

now do to get this to make it something im looking for?

 

Pardon?

[xcoderx]

lol half of wht i wrote did not appear funny. anyways its solved. thanks