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.

  • #2
    New Coder
    Join Date
    Jul 2006
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try this
    Code:
    $category_id = $_REQUEST['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";
    Good luck!

  • #3
    Regular Coder
    Join Date
    Dec 2005
    Posts
    346
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I am not getting anything displayed w/what you have. How do I display every record found using that query on the page?

  • #4
    New Coder
    Join Date
    Jul 2006
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Your initial question was just regarding construction of the sql statement. You would want to get the results from the query and display it accordingly.

    You would have to create a loop and format how you'd want the values to be displayed.

    Just so you know that you're retrieving values add this
    Code:
    var_dump (@mysql_fetch_assoc($rslt));
    Good luck!

  • #5
    Regular Coder
    Join Date
    Dec 2005
    Posts
    346
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by martialtiger View Post
    Your initial question was just regarding construction of the sql statement. You would want to get the results from the query and display it accordingly.

    You would have to create a loop and format how you'd want the values to be displayed.

    Just so you know that you're retrieving values add this
    Code:
    var_dump (@mysql_fetch_assoc($rslt));
    Good luck!
    I was using the first code below & it wouldn't work so that is why I was asking for more help. You can see in the other code sample that I added what you said & it didn't work. I might not have added it correctly so if I didn't add it where it should be.

    I know it isn't the number_format() function since the product_id wont even display.

    PHP Code:
            while ($page mysql_fetch_array($qry)) 
            {
               
    $commision_pd $page['product_id'];
               
    $display_page_paid_nds number_format($page['products_price'];, 2);
               
    $display_page_paid_wds "$ " number_format($page['specials_new_products_price'];, 2);
            } 
    PHP Code:
            while ($page var_dump (@mysql_fetch_assoc($qry))) 
            {
               
    $commision_pd $page['product_id'];
               
    $display_page_paid_nds number_format($page['products_price'];, 2);
               
    $display_page_paid_wds "$ " number_format($page['specials_new_products_price'];, 2);
            } 
    PHP Code:
            while ($page = @mysql_fetch_assoc($qry)) 
            {
               
    $commision_pd $page['product_id'];
               
    $display_page_paid_nds number_format($page['products_price'];, 2);
               
    $display_page_paid_wds "$ " number_format($page['specials_new_products_price'];, 2);
            } 

  • #6
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts
    PHP Code:
    while ($page var_dump (@mysql_fetch_assoc($qry)))  
            { 
               
    $commision_pd $page['product_id']; 
               
    $display_page_paid_nds number_format($page['products_price'];, 2); 
               
    $display_page_paid_wds "$ " number_format($page['specials_new_products_price'];, 2); 
            } 
    Martial was stating for you to add that as a debugging feature. You can add it exactly how they typed it.

    PHP Code:
    var_dump(@mysql_fetch_assoc($qry); 
    Also, you would have faired better by posting this is the MySQL portion of this site as your problem isn't with the PHP. I suggest that you get the query working in MySQL before trying to implement it in your PHP. Have a look at this page concerning joins written by CF's own Guelphdad. Join Basics Also, you might want to read some of the other articles as well.
    Last edited by StupidRalph; 01-31-2007 at 02:49 AM.
    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.


  •  

    Posting Permissions

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