#1
  1. from the lab...
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Nov 2004
    Location
    the land of wind and ghosts
    Posts
    1,844
    Rep Power
    560

    Parent/Child and product through mysql only


    G'day!

    Struggling a bit with this query, trying to see if I can do it with straight mysql, or if I need to use php.

    I've got a 'category' table, which has parent/child relationships contained within, and a 'products' table in which each product is assigned a single category, which could potentially have a couple of parent categories above.

    Here's what I've got, and it pulls one parent level deep, but I need more:
    PHP Code:
    $sql 
    'SELECT 
        a.product_id, a.title, a.price, a.photo
        ,
        b.category_id, b.category
    FROM
        shq_products a
    INNER JOIN
        shq_categories b
    ON
        b.category_id = a.category
    WHERE
        a.active = 1            
    '
    ;
    if(
    $cat) {
    $sql .= '
    AND
        b.category_id = '
    .mysql_real_escape_string($cat).'
    OR
        b.parent_cat_id = '
    .mysql_real_escape_string($cat).'
    AND
        a.active = 1
    LIMIT
        '
    .$cur.' , '.$max.'
    '
    ;
    } else {
    $sql .= '
    ORDER BY RAND()
    LIMIT 6
    '

    Thank you!
  2. #2
  3. Wiser? Not exactly.
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    5,932
    Rep Power
    4033
    Have a look through this article:
    Managing Hierarchical Data in MySQL
    Recycle your old CD's, don't just trash them



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud

IMN logo majestic logo threadwatch logo seochat tools logo