php - how to update a product in database

Question

i am trying to update a product in mysql DB

here is my product table structure : [table product structure]

it has a foreign key id_category that references to the table category's id : [table category structure]

and this my code :

if(isset($_POST['submit'])){
            $id=htmlspecialchars($_POST['id']);
            $name=htmlspecialchars($_POST['name']);
            $photo=htmlspecialchars($_POST['photo']);  
            $quantity=htmlspecialchars($_POST['quantity']);
            $price=htmlspecialchars($_POST['price']);
            $description=htmlspecialchars($_POST['description']);
            $category=htmlspecialchars($_POST['category']);
            $brand=htmlspecialchars($_POST['brand']); 
            $obj=new Connexion();
            $cnx=$obj->getconnexion();
            //sql statement
            $sql="update product  set id=$id,name='$name',photo='$photo',quantity=$quantity,price=$price,description='$description',category=(select id_category from category where category_name='$category'),brand='$brand' where id=1";
            $res=$cnx->exec($sql);
            if($res)
            { header("location:findall.html.php");}
            else echo "modification problem!!";

when i try it, it states

modification problem!!

the POST method works fine but i can't figure out where's the error in the sql statement.

Answer 1

you should change "category" to "id_category" in your query

id_category=(select id_category from category where category_name='$category')