Tapan
August 30, 2013, 11:52am
1
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.
r937
August 30, 2013, 6:25pm
2
Tapan
August 31, 2013, 8:43am
3
Hi,
Excellent. Works like a charm.
Thank you.
Tapan
April 18, 2014, 8:34am
4
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.
r937
April 18, 2014, 12:00pm
5
because your table does not include a row with catg_id = 0
Tapan
April 22, 2014, 1:56pm
6
Hi,
Then whats the solution to this ? How do i fix it ?
Thanks.
r937
April 22, 2014, 5:18pm
7
go back to the article from post #2 and look at the sample data to see how it is done