0

I have two tables, CATEGORIES and PRODUCTS, with category_id as a foreign key in PRODUCTS. How can I update a field in PRODUCTS table that automatically update the foreign key too? I want to update the category by category_name with html select box.

This the function updateProduct in my CRUD.php

public function updateProduct($id, $product_name, $product_price, $category_name) {
    $sql = "UPDATE products INNER JOIN categories ON products.category_id = categories.category_id SET product_name = '$product_name', product_price = '$product_price' category_name = '$category_name' WHERE product_id = $id";
    $stmt = $this->conn->prepare($sql);
    $stmt->execute();
    return true;
}
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Puput Bella
  • 1
  • 3
  • You foreign key __must be__ `category_id` in `products` table. And `category_name` must be field in __`categories`__ table and __nowhere else__. – u_mulder Oct 02 '19 at 11:21
  • `$sql = "UPDATE products INNER JOIN categories ON products.category_id = categories.category_id SET product_name = '$product_name', product_price = '$product_price' category_name = '$category_name' WHERE product_id = $id"; $stmt = $this->conn->prepare($sql);` looking into the code i would suggest to read [this](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) , as that is not the way to use prepare and bind variables.. – Raymond Nijland Oct 02 '19 at 11:22
  • *"How can I update a field in PRODUCTS table that automatically update the foreign key too? "* Run `SHOW CREATE TABLE ` for the parent and child table and include that output in the question ...Also see [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) – Raymond Nijland Oct 02 '19 at 11:26
  • 1
    Your not using prepared statements properly. Please read this https://phpdelusions.net/pdo/cargo_cult_prepared_statement – Dharman Oct 02 '19 at 11:29
  • This is basically a MySQL question that has nothing to do with PHP, PDO, OOP and CRUD. – Your Common Sense Oct 02 '19 at 11:44

0 Answers0