How to get products from all subcategories including the parent category?

Hi,

I have this category / subcategory table, based on this what will be my query to get products from a specific top level category and all its subcategories ?

CREATE TABLE IF NOT EXISTS `categories` (
  `catg_id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `catg_pid` mediumint(9) NOT NULL DEFAULT '0',
  `catg_name` varchar(30) NOT NULL,
  `catg_description` varchar(255) NOT NULL,
  `catg_status` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`catg_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

Thanks.

maybe this will help… http://sqllessons.com/categories.html

Hi,

Excellent. Works like a charm.

Thank you. :slight_smile:

Hi,

I am having issues with this. I read the article and accordingly prepared my table which is following:

CREATE TABLE IF NOT EXISTS `categories` (
  `catg_id` int(11) NOT NULL AUTO_INCREMENT,
  `catg_pid` int(11) DEFAULT NULL,
  `catg_url` varchar(50) NOT NULL DEFAULT '',
  `catg_name` varchar(50) NOT NULL,
  `catg_description` text NOT NULL DEFAULT '',
  `catg_picture` varchar(50) NOT NULL DEFAULT '',
  `catg_status` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`catg_id`),
  FOREIGN KEY catg_pid_fk (catg_pid) REFERENCES categories (catg_id)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Now when I am trying to add records I am getting an error:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`store`.`categories`, CONSTRAINT `categories_ibfk_1` FOREIGN KEY (`catg_pid`) REFERENCES `categories` (`catg_id`))

My insert sql is like this:

INSERT INTO categories SET 
			catg_pid = '0',
			catg_name = 'Category 1',
			catg_description = '',
			catg_status = '1';

Why I am getting that error ??? Please suggest.

Thanks.

because your table does not include a row with catg_id = 0

Hi,

Then whats the solution to this ? How do i fix it ?

Thanks.

go back to the article from post #2 and look at the sample data to see how it is done

:slight_smile: