SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    564
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    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 ?

    Code:
    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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    maybe this will help... http://sqllessons.com/categories.html
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    564
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Hi,

    Excellent. Works like a charm.

    Thank you.

  4. #4
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    564
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

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

    Code:
    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:

    Code:
    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:

    Code:
    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.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Tapan View Post
    Why I am getting that error ???
    because your table does not include a row with catg_id = 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    564
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

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

    Thanks.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Tapan View Post
    Then whats the solution to this ? How do i fix it ?
    go back to the article from post #2 and look at the sample data to see how it is done

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •