CODEWITHSUNDEEP
phpmysql
Rob
Rob

Reputation: 147

Update one table where record found in another table

I am really struggling on how to achieve this and hope that someone can help me. I have the existing function and query:

public function setStockAndPrice($product_id) {
  $query = $this->db->query('UPDATE ' . DB_PREFIX . 'product SET quantity = 0, price = 0.00 WHERE product_id = ' . (int)$product_id)
}

This works, but it sets all products to Zero when I actually want it to only set products to Zero when that product exists in another table.

i.e., in explanatory terms:

public function setStockAndPrice($product_id) {
  $query = $this->db->query('UPDATE ' . DB_PREFIX . 'product SET quantity = 0, price = 0.00 WHERE product_id = ' . (int)$product_id AND product_id exists in CustomerProducts)
}

I am not au fait with joins, but I am not sure if I even need to use a join here as the query seems more simple than that.

Can anyone point me in the right direction?

Upvotes: 0

Views: 158

Answers (3)

abhij89
abhij89

Reputation: 625

  public function setStockAndPrice($product_id) {
  $query = $this->db->query('UPDATE ' . DB_PREFIX . 'product SET quantity = 0, price = 0.00 WHERE product_id = ' . (int)$product_id ." AND product_id =(select DISTINCT(product_id) from CustomerProducts where product_id= $product_id)" )
  }

This may work.

Upvotes: 2

Sachem
Sachem

Reputation: 491

public function setStockAndPrice($product_id) {
      $query = $this->db->query('UPDATE ' . DB_PREFIX . '.product p, ' . DB_PREFIX . '.CustomerProducts cp SET p.quantity = 0, p.price = 0.00 WHERE p.product_id = ' . (int)$product_id . ' AND p.product_id = cp.product_id');
    }

Upvotes: 0

Rafee
Rafee

Reputation: 4078

use this will work for you are not assigning db.product and make sure you write query in a string then execute.

And you see you query, by removing comments

public function setStockAndPrice($product_id) {
  $query_string = "UPDATE " . DB_PREFIX . ".product SET quantity = '0', price = '0.00' WHERE product_id = '$product_id'";

  // echo "Query : " . $query_string;

  $query = $this->db->query($query_string);
}

Upvotes: 0

Related Questions