Is my query syntax correct?

[ballouta]

Hello

 

I have two tables, one called order_products with ten attriubtes, and the table products with many different attributes one of them is "weight"

 

the original query selects all of the ten colums based on a given order number:

$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_product WHERE order_id = '" . (int)$order_id . "'");

I am trying to modify the ablove query to get the weight of each product that (weight) is in products table, I wrote this query

that worked well on phpMyAdmin and gave me the correct result,. However, when I use the query in the original context of OpenCart

it has to have the $order_id avraible, and I wanna make sure I ahve the correct syntax, because writing the correct sytnax is not enough yet to test my code, I have to edit other arrays and stuff! thank you

		$query = $this->db->query("SELECT OP.order_product_id, OP.order_id, OP.product_id, OP.name, OP.model, OP.price, OP.total, OP.tax, OP.quantity, OP.subtract, P.weight FROM dmc9strorder_product OP, dmc9strproduct P 
		WHERE OP.order_id = '" . (int)$order_id . "' AND OP.product_id = P.product_id" );

[Psycho]

1. Don't put numeric IDs in quotes

2. Use JOINs

 

 

$order_id = intval($order_id);
$sql = "SELECT OP.order_product_id, OP.order_id, OP.product_id, OP.name, OP.model,
               OP.price, OP.total, OP.tax, OP.quantity, OP.subtract, P.weight
        FROM dmc9strorder_product OP
        JOIN dmc9strproduct P ON OP.product_id = P.product_id
        WHERE OP.order_id = $order_id";
$query = $this->db->query($sql);

[ballouta]

Thank you Psycho, very helpful