Trouble Updating Table with Multiple Rows with Different Values

[Becca Duran]

I have two tables product and order_product. Each contain a number of columns as follows:

product: product_id, name, price
order_product: order_product_id, order_id, product_id, name, quantity, price, total

When the table product updates the "price" column I need the order_product table to update the price and then update the total to reflect the new price.

I have successfully written a code to update the "price" column in the table order_product; however, I am having issues updating the total. Any help would be appreciated.

The $product_id variable has already been declared earlier in the function. The $order_product_id has not. I know that "$result->row" only selects the first row; however if I exchange that for "$result->rows" I receive an "Undefined index" error. So, not sure what to do about that.


//Update Order_Product Price**This Works//

$product_qry = $this->db->query("SELECT price FROM " . DB_PREFIX . "product WHERE product_id = '" . (int)$product_id . "'");
$new_price = $product_qry->row['price'];
$this->db->query("UPDATE " . DB_PREFIX . "order_product SET price = '" . $new_price . "' WHERE product_id = '" . (int)$product_id ."'");

//Update Order_Product Total**This Doesn't Work//

$order_product_id_qry = $this->db->query("SELECT order_product_id FROM " . DB_PREFIX . "order_product");
$order_product_id = $order_product_id_qry->row['order_product_id'];
$order_qry = $this->db->query("SELECT quantity FROM " . DB_PREFIX . "order_product WHERE order_product_id = '" . (int)$order_product_id . "'");
$product_quantity = $order_qry->row['quantity'];
$new_total = $product_quantity * $new_price;
$this->db->query("UPDATE " . DB_PREFIX . "order_product SET total = '" . $new_total . "' WHERE order_product_id = '" . (int)$order_product_id . "'");