Hello and welcome to our community! Is this your first visit?
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
    Thanked 0 Times in 0 Posts

    Selecting and joining troubles...


    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


  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Thanked 4,636 Times in 4,598 Posts
    $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