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 6 of 6
  1. #1
    Regular Coder
    Join Date
    Dec 2005
    Posts
    346
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Help making this unique db query

    I have spent hours & hours on this query & can't get it to work. I have three tables to use in this query. I have the following tables to use.

    table_a houses the categories_id, parent_id
    table_b houses the products_id, categories_id
    table_c houses the products_id, products_price
    table_d houses the products_id, products_name
    table_e houses the products_id, specials_price, start_date, end_date

    I am posting a category_id number from a form.

    I need to query table_a for all categories_id where the parent_id equals the posted category_id. This is simple to do.

    I then need to query table_b for products_id where the categories_id equals the categories_id pulled from table_a.

    I then need to query table_c for products_price where the products_id equals the products_id pulled from table_b.

    I then need to query table_d for products_name where the products_id equals the products_id pulled from table_b.

    I then need to query table_e for specials_price, start_date & end_date where the products_id equals the products_id pulled from table_b.

    This is pulling all products from the database so I need to be able to list them on a page. I do know this is a complicated query & I would appreciate any & all help that I could get.

    I am just not experience in using join statements in my MySQL query statements.

    Someone else in the php section said this should have worked, but it didn't.
    PHP Code:
    $category_id $HTTP_POST_VARS['category_id'];
    $qry "SELECT a.categories_id, b.products_id, c.products_price, d.products_name, e.specials_price, e.start_date, e.end_date
    FROM table_a a, table_b b, table_c c, table_d d, table_e e
    WHERE a.parent_id = '$category_id'
    AND b.categories_id = a.categories_id
    AND c.products_id = b.products_id
    AND d.products_id = c.products_id
    AND e.products_id = d.products_id"


  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    In my opinion tables b, c and d should all be a single table.

  • #3
    Regular Coder
    Join Date
    Dec 2005
    Posts
    346
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by guelphdad View Post
    In my opinion tables b, c and d should all be a single table.
    You are sooo right. I don't know why the previous person make this system like this. I wish table c & d was together, but the table b needs to be separate since this system allows us to copy products to be displayed in several categories if the product is useful in more than one category. This table only houses the products_id & its category_id. This way if I want the products_id 10 to show in category 4 & 35 & just enter two new records into this table. I think this would be easier than listing the categories in the db separated by commas then using explode(",",$category_id) to find the categories.

    Can you help me with this anyways?

  • #4
    Regular Coder
    Join Date
    Dec 2005
    Posts
    346
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have been messing around with this learning a little about join. I have this code below that seems to work, but it list every record twice. I have just posted the raw code as I edited out certain data on the first post.


    In that LEFT JOIN () I put the categories_id or parent_id since I have subcategories. My categories go 3 deep...I have Balls,-> Color -> Brands. If I query for all Brands I get data returned, if I query for Color I get data returned, but if I query the main category Balls & get a blank screen.

    This prices aren't right either...I don't even have the MilTec Tactical Blue Paintballs in the specials table yet. It has never been on sale before so it shouldn't even have a sale price.

    PHP Code:
          $specials_qudery tep_db_query("select ptc.products_id, ptc.categories_id, pd.products_name, p.products_price, s.specials_new_products_price
                                              from " 
    TABLE_PRODUCTS_TO_CATEGORIES " ptc, " TABLE_PRODUCTS_DESCRIPTION " pd, 
                                            " 
    TABLE_PRODUCTS " p, " TABLE_SPECIALS " s LEFT JOIN " TABLE_CATEGORIES " c on (c.categories_id = '" $HTTP_POST_VARS['categories_id'] . "' or c.parent_id = '" $HTTP_POST_VARS['categories_id'] . "')
                                            where pd.products_id = ptc.products_id and ptc.categories_id = c.categories_id and p.products_id = ptc.products_id"
    );
          
          while (
    $producdt tep_db_fetch_array($specials_qudery)) {
            
            echo 
    '<hr>Category ID: s' $producdt['categories_id'] . ' - Product Name: ' $producdt['products_name'] . ' - List: ' $producdt['products_price'] . ' - Sale: ' $producdt['specials_new_products_price'] . '<hr>';
          } 
    This is what is being displayed...
    Category ID: s19- Product Id: 30 - Product Name: Nelson Anarchy Chaos Paint Blue Shell Yellow Fill - List: 59.9900 - Sale: 50.9915
    Category ID: s19- Product Id: 33 - Product Name: Protection Plan 1099 - List: 50.0000 - Sale: 50.9915
    Category ID: s18- Product Id: 34 - Product Name: MilTec Tactical Blue Paintballs - List: 59.9900 - Sale: 50.9915
    Category ID: s19- Product Id: 30 - Product Name: Nelson Anarchy Chaos Paint Blue Shell Yellow Fill - List: 59.9900 - Sale: 250.0000
    Category ID: s19- Product Id: 33 - Product Name: Protection Plan 1099 - List: 50.0000 - Sale: 250.0000
    Category ID: s18- Product Id: 34 - Product Name: MilTec Tactical Blue Paintballs - List: 59.9900 - Sale: 250.0000

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Read this article on using JOINS. Don't mix list syntax and OUTER JOINS.

    Read this article so you can understand categories and subcategories.

    Now instead of trying to do everything at once join tables A and B above and get the info you need out of them. Got that far? Good. Now add table C. Is that working correctly? Then add successive tables until you get all the information you need.

    When something "doesn't work" that doesn't tell folks exactly what the problem is. Pinpoint things with error messages or an explanation beyond that so folks know where the problem might lie.

    Oh yes, last, but not least, take the time to format your posts so that they are legible and don't require left/right scrolling as well as up/down. It is easier if you keep terms all on one line like in the JOINS article posted above.

  • #6
    Regular Coder
    Join Date
    Dec 2005
    Posts
    346
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I read over those links. Some of it I understand...I have this code below that I copied part of the code from that Categories & Subcategories page you gave. I am going to get the data from table a & b then try for the rest.

    I am trying to get all categories id's below the category id that I am posting. I might post a parent id & then I might post a 3 level deep id. The code I have below does pull the categories id's for every category right below the category id I posted & it then gets the products id for those categories it found. Can you help me with the code to get it to pull the other levels below?

    The part about the code not working...I just get blank page with no errors to provide. I did test this & I can echo before the loop statement, but after it it wont display any data & that is where the page contents is & I think that is why I get a blank page.

    PHP Code:
    select 
    root
    .categories_id
    root.parent_id
    down1.products_id
    from 
    " . TABLE_CATEGORIES . " as root
    left outer join 
    " . TABLE_PRODUCTS_TO_CATEGORIES . " as down1
    on root
    .categories_id down1.categories_id
    where root
    .parent_id '" . $HTTP_POST_VARS['categories_id'] . "' 


  •  

    Posting Permissions

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