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 2 of 2
  1. #1
    New to the CF scene
    Join Date
    May 2011
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selecting and joining troubles...

    Hi,

    I'm currently setting up a small shop using PHP & MySql and have been stuck all day on one small feature.


    I basically want to display an order which has been placed in the database.

    From this I need to take information from 2 tables, 'order_prod' and 'products'.

    In 'products' I have product_id, product_name, product_desc and product_price

    In 'order_prod' I have order_id, product_id, order_quan


    One benefit is that when the user is on this page I've made it so they already have the order_id in the address bar, so this can be brought into the code using '$_GET['order_id']'.

    My problem is just linking them so that I can show the product_name, product_desc, product_price from 'products' and order_quan from 'order_prod' which are relevant to the order_id


    If anyone can help I appreciate it, this is making my brain turn inside out as the answer is probably simple

    Thanks!

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,506
    Thanks
    77
    Thanked 4,378 Times in 4,343 Posts
    Code:
    $sql = "SELECT P.product_id, P.product_name, P.product_desc, P.product_price, O.order_quan, P.product_price * O.order_quan AS subtotal "
         . " FROM products AS P, order_prod AS O "
         . " WHERE P.product_id = O.product_id "
         . " AND O.order_id = " . $_GET["order_id"] 
         . " ORDER BY P.product_name";
    
    ... now execute that query and get records from it ...
    The ORDER BY is arbitrary. You could ORDER BY whatever field you prefer.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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