Updating "Stock" Column after a purchase is completed-CodePudding

For my Restaurant Management System I'm trying to create a Stock Table where I could update, increase and delete amount of stocks. When a order is placed from the frontend I want to decrease the stock amount and update the table.

I have a table that would hold the cart data. And another table that holds the information for Menu. It has columns such as title, description, price, stock etc.

So far I've done this:

For Cart table

mysqli_stmt_bind_param($stmt,"isii",$Order_Id,$Item_Name,$Price,$Quantity);

            foreach($_SESSION['cart'] as $key => $values)
            {
                $Item_Name = $values['Item_Name'];
                $Price = $values['Price'];
                $Quantity = $values['Quantity'];
                
                mysqli_stmt_execute($stmt);

            }

And this is the query to update Stock value

$update_quantity_query = "UPDATE tbl_food SET
                                     stock = stock - $Quantity
                                     WHERE title = $Item_Name
                                    ";

            $res_update_quantity_query = mysqli_query($conn, $update_quantity_query);

I'm getting this error:

Uncaught mysqli_sql_exception: You have an error in your SQL syntax; right syntax to use near 'Dog' at line 3.

What am I doing wrong?

CodePudding user response：

You Need to wrap the string inside single quotes if you are wrapping your query inside double quotes and vice versa.

So the query would be:

UPDATE tbl_food SET stock = stock - $Quantity WHERE title = '$Item_Name'

Also, make sure the column stock's datatype is INT And not VARCHAR

I Would also like to suggest implementing this feature after the order is delivered and not after it is placed. this is from my personal experience, but you are free to follow your own methods.

If you face any problems with SQL Queries later on or whenever you face it, try printing the SQL Query instead of executing it.

$query = "YOUR QUERY HERE";
echo $query;

So you would see what's wrong with your query. you could also execute that in the Terminal / PHPMyAdmin GUI to get a detailed description of the problem.