Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Oct 2003
    Location
    AZ
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Compound MySQL Satements

    I'm trying to select products in a category and the products in subcategories of that category.

    My category table looks something like below where each category has a parentid - which it is a subcategory of.

    Data
    Code:
    Table: Categories
    +-----+----------+--------+
    |  id | parentid |  name  |
    +-----+----------+--------+
    |  1  |    0     | first  |
    +-----+----------+--------+
    |  2  |    1     | second |
    +-----+----------+--------+
    |  3  |    1     | third  |
    +-----+----------+--------+
    |  4  |    2     | fourth |
    +-----+----------+--------+
    |  5  |    3     |  fifth |
    +-----+----------+--------+
    
    The above data is displayed like such:
    
    first
       + second
            + fourth
       + third
             + fifth
    Code:
    Table: Products
    +--------------+-------------------+---------------+
    |     id       |    categoryid     |     name      |
    +--------------+-------------------+---------------+
    |     1        |          4        |     shoe      |
    +--------------+-------------------+---------------+
    If I'm in the fourth category then I need a query that will select products with the categoryid of fourth,second and first.

    If i'm in the third category then the query needs to select products with the categoryid of third and first.

    Kind of hard to explain, I hope what I am trying to do is clear.

    But some how I need the query to see if there are parent categories and select products with those categoryid's as well.
    Last edited by coffeedemon; 04-12-2011 at 05:14 PM.
    PHP | FreeBSD | MySQL |
    Check documentation... check documentation again and actually read it..do it... doesn't work ask.

  • #2
    New Coder
    Join Date
    Oct 2003
    Location
    AZ
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is what I am doing it. I was hoping there is a way to do it in a single MYSQL query.

    PHP Code:
    function compound_categories($categoryid$iii=0) {
                    
    $iii++;
                    if(
    $iii==10) { echo 'killed'; exit; }
                    
                    
                    
    $sql        "SELECT id,parent FROM categories WHERE parent='"$categoryid ."'";
                    
    $results    mysql_query($sql)or die(mysql_error());
                    
    $row        mysql_fetch_array($results);
                    
    $count        mysql_num_rows($results);
                    
    $array[]    = $row['id'];
                    
                    if(
    $count != 0) {
                        
    $array[] = compound_categories($row['id'], $iii);
                        
                    }
                    if(
    $iii == 1) {
                        return 
    $array;
                    } else {
                        return 
    $row['id'];
                    }
                }
                if(isset(
    $_GET['category'])) {
                    
    $categoryid_array compound_categories($_GET['category']);
                    
    $query_where_clause =  "WHERE categoryid='"$_GET['category'] ."'";
                    foreach(
    $categoryid_array as $key => $value) {
                        
    $query_where_clause .=     " or categoryid='"$value ."'";
                    }
                    echo 
    $sql "SELECT * FROM products AS t2 "$query_where_clause ." ORDER BY date DESC";
                } 
    Last edited by coffeedemon; 04-12-2011 at 07:52 PM.
    PHP | FreeBSD | MySQL |
    Check documentation... check documentation again and actually read it..do it... doesn't work ask.

  • #3
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    maybe this will help:

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

    best regards

  • #4
    New Coder
    Join Date
    Oct 2003
    Location
    AZ
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Excellent thank you!
    PHP | FreeBSD | MySQL |
    Check documentation... check documentation again and actually read it..do it... doesn't work ask.

  • #5
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by coffeedemon View Post
    Excellent thank you!
    you are welcome, and thank you,
    I search few years ago information about this subject, i was mainly interested in graphs then trees. Another related links:

    http://evolt.org/node/4047/
    http://www.phpro.org/tutorials/Manag...and-MySQL.html
    http://blogs.sitepoint.com/hierarchical-data-database/

    best regards


  •  

    Posting Permissions

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