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);
?>
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.
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);
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.