View Full Version : Left join query

01-22-2012, 06:14 PM
Hi Guys,

Im having trouble getting a query to work.

I need it to get all the fields from table 'order_product' with the where clause and with the results also all the details from table 'product' where the product_id fields match.

This is what I have so far but am stuck now as its not working :

$order_product_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_product WHERE order_id = '" . (int)$order_id . "' LEFT JOIN product "."ON product_id = order_product.product_id");


Old Pedant
01-23-2012, 01:08 AM
You have to use legal SQL syntax.

You can NEVER have the WHERE clause before the JOIN.

$sql = "SELECT * FROM " . DB_PREFIX . "order_product "
. " LEFT JOIN product ON product.product_id = order_product.product_id "
. " WHERE order_product.order_id = " . (int)$order_id;

echo "<hr/>DEBUG SQL: " . $sql . "<hr/>\n";

$order_product_query = $this->db->query( $sql )

It's also not a good idea to use SELECT * any time, but especially not when you do a JOIN. The field product_id (at a minimum) will appear twice in the SELECT list when you do so, which can badly confuse your subsequent PHP code.

I strongly recommend you change SELECT * to SELECT only the fields your PHP code will be using.

And not to ask a dumb question, but... Why do you need the DB_PREFIX on the one table but not on the other table?

01-29-2012, 07:47 PM
Hi thanks for replying.

In this query I want to draw all the data from the table 'order_product' and just the data in the column 'image' from the table 'product'.

What is the best way to write this then so I dont select too much unecessary data ? :)

Old Pedant
01-29-2012, 11:24 PM
$sql = "SELECT op.*, p.image FROM " . DB_PREFIX . "order_product AS op "
. " LEFT JOIN product AS p ON p.product_id = op.product_id "
. " WHERE op.order_id = " . (int)$order_id;

Same question re DB_PREFIX applies.