Stuck on Joins?

[coxdabd]

Hi all, I'm wondering if there is a better way to join all three of these queries to become one. Any ideas or suggestions would be greatly appreciated. Please see below my existing code:

<?php
$sql = mysql_query("SELECT * FROM customers WHERE customer_ref='$_GET[id]'");
    $sql = mysql_fetch_array($sql);
        $prods = mysql_query("SELECT * FROM order_products WHERE receipt_id = '$_GET[ref]' AND customer_id = '$_GET[id]'");
            $orderInfo = mysql_query("SELECT * FROM orders WHERE cust_ref='$_GET[id]' AND receipt_id='$_GET[ref]'");
                $orders = mysql_fetch_array($orderInfo);
?>

[r937]

you're retrieving everything from three different tables using the dreaded, evil "select star"

how do we know how the tables are related? and whether they can actually be joined to return what you want? and which columns you really need?

[coxdabd]

Hey there, I know I should select only the columns I need to use, I'll make sure this happens. orders and order_products both have the same column 'receipt_id'. This is unique to each other placed. I then also need to grab the customers ref (their id). Thinking about it, the only tables I can join (I think) are orders and order_products. I'll have to query the table 'customers' separately.

[r937]

are you saying you accept an order but you don't know which customer ordered it?

[coxdabd]

Hi, yes I do know, I'm passing their ID through the browser. It's only a small inhouse system that I'm developing. I think I have managed to join all the tables but now one of my while loops does not work (though that's a separate matter).

$sql = mysql_query("SELECT order_products.* , orders.*, customers.* FROM orders LEFT JOIN order_products ON orders.receipt_id = order_products.receipt_id LEFT JOIN customers ON customers.customer_ref=order_products.customer_id WHERE orders.receipt_id='$_GET[ref]' AND orders.cust_ref='$_GET[id]'");
    $sql = mysql_fetch_array($sql); 

[r937]

a single order can be for multiple order products, and each order_product has its own customer?

i am afraid i completely fail to understand your tables

[guelphdad]

Show the design of your tables and provide sample inserts for each as well as the expected output. this will be easier for you to obtain assistance with your problem.