categories - products how to build the menu three??

Hi

I have the following problem:

I have categories and products tables. Here they are:

Categories:

CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(37) NOT NULL,
  `parentid` int(11) DEFAULT NULL,
  `description` text NOT NULL,
  `metatags` text NOT NULL,
  `sorder` int(11) NOT NULL,
  `visible` tinyint(4) NOT NULL,
  `categoryphoto` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `parentid_fk` (`parentid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=31 ;

Code (markup):

And here is my products table:

CREATE TABLE IF NOT EXISTS `products` (
  `productid` int(11) NOT NULL AUTO_INCREMENT,
  `catid` int(11) NOT NULL,
  `productcode` varchar(255) DEFAULT NULL,
  `productname` varchar(255) DEFAULT NULL,
  `shortdescription` text,
  `description` text,
  `price` varchar(255) DEFAULT NULL,
  `productphoto` varchar(255) DEFAULT NULL,
 
  PRIMARY KEY (`productid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=58 ;

Code (markup):

I would like to build my menu three using those tables.

First, I would like to get first root category (which has parent id NULL) and to check if this category has sub categories. Then, if there are any, I should display them one by one, and for each of this categories I have to make the same check, that is if any of those categories has subcategories underneath.

Theoretically there may be unlimited levels of categories-subcategories.

If there are no, then I should display the products for each category.

I will appreciate if anyone can help me with this task. Links to tutorials for this topic will aslo do the trick.

Regards, Zoreli

 

Your table 'categories' isn't optimized.

While you developing your DB model, keep in mind how you're going to use it.

In this case, the best practice will be to introduce one more table called category_category_mm. There will be two important fields: category_id and parent_id. This way you can easily find all child/parent categories of a category.