How do I substract values from product table and order table?

Question

0.00/5 (No votes)

See more:

I am trying to change my stock quantity of products where admin confirm the order of the customer and the product quantity will change according to the quantity added from the order table.

What I have tried:

i tried this but there are no changes. Nothing works.

PHP

function upOrder(){
global $db;
    $uid = $_SESSION['user_id'];
    $oid=intval($_GET['oid']);
    $pid=intval($_GET['pid']);
    $status=$_POST['status'];

                $sql=mysqli_query($db,"update orders set payment_status='$status', confirmDate = CURRENT_TIMESTAMP where id='$oid'");

                if($status == 'Completed'){

                $result =mysqli_query($db,"SELECT productQty FROM products WHERE id = '$pid'");
                $query =mysqli_query($db,"SELECT * FROM orders WHERE id = '$oid' AND user_id = '$uid' AND product_id = '$pid'");

                $productRow = $result->fetch_assoc();
                $orderRow = $query->fetch_assoc();

                $newqty = $productRow['productQty'] - $orderRow['quantity'];
                $wow =mysqli_query($db,"UPDATE products SET productQty = '$newqty' WHERE id = '$pid'");

                if($wow){

                    echo "alert('Order updated sucessfully...');";

                }else{

                    echo "alert('Failed!');";

                }
                }

}

Posted 27-Apr-20 12:57pm

amimranse7en

Updated 27-Apr-20 15:32pm

MadMyche

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MadMyche · Accepted Answer · 2020-04-27T15:34:00

I have 2 problems with the code you have presented; and that is not even looking into the troubleshooting aspect of trying to figure out why it doesn't work.

The first problem is that your style of creating an SQL query is ripe for SQL Injection! You should NEVER EVER create a query by concatenating commands with string values.
I will acknowledge that there is some rudimentary protections in place, it is not something that will always be available and it should not be relied upon.
The preferred method for PHP/MySql is to use what are known as Prepared Statements. Your query is written with placeholders and then you can bind your variables to the placeholders.
There is plenty of documentation on how to implement this, such as this
w3schools article[^]

The second thing I do not care for is the excess queries you are running; it is excess overhead in the application and extra network traffic to/from the database server. You have 4 separate queries, but it could be pared down to just the 2 update queries.
What many people don't know is that you can use JOINs within an update.
Roughly translated from your two SELECT queries, I think you could implement something based on this for your update statement.

Please note that this is written for SQL Server, which uses @Variable naming; and that PHP uses indexed question marks. I left this for you to change as this way you can see which of your PHP variables belong where in the query

SQL

UPDATE p
SET    p.ProductQty = p.ProductQty - o.Quantity
FROM   Products   p
INNER JOIN Orders o ON p.id = o.product_id
WHERE o.id         = @oid -- placeholders
AND   o.user_id    = @uid
AND   o.product_id = @pid