-1

I want to run update query using for loop and while loop. Because I want to update quantity of product in product table after success of order according to product id and total quantity after subtracting order quantities from total quantity.But the query does not run successfully.

My Code:

           // Update Query 
            $update_quantity = "";
            for ($i = 0; $i < (count($_SESSION['shopping_cart'])); $i++) {

                $select_quan = "SELECT * FROM product WHERE pid = {$pid[$i]}";
                $result_quan = mysqli_query($con, $select_quan);

                // $update_quantity .= " UPDATE product SET ";

                while ($row_quan = mysqli_fetch_assoc($result_quan)) {

                    $quantity_after_order[$i] =   $row_quan['pquantity'] - ($pquantity[$i]);

                    $update_quantity .= "UPDATE product SET pquantity = '$quantity_after_order[$i]' WHERE pid=$pid[$i]; ";
                }
            }

When I Run Query:

            // echo $update_quantity;
            if ($con->query($update_quantity) == TRUE) {
                 echo "Data Successfully Updated";
             }
             else{
                  echo "Error: $update_quantity <br> $con->error";
             }

Output It Gives:

Error: UPDATE product SET pquantity = '246' WHERE pid=7; UPDATE product SET pquantity = '11' WHERE pid=32;

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE product SET pquantity = '11' WHERE pid=32' at line 1

If Another Way to run this query which updates quantities in database according to product_id after order success so plz tell me.

msaad dev
  • 31
  • 4
  • 11
  • [See How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Also use [multi table UPDATE](https://dev.mysql.com/doc/refman/8.0/en/update.html) syntax so you don't need to do SQL in loops. – danblack Feb 02 '21 at 04:50
  • Is pquantity a String ? – Mohd Alomar Feb 02 '21 at 05:30
  • 3
    SQL shines when you tell it to act on a set of rows "all at once"; SQL bogs down when you do things one row at a time. – Rick James Feb 02 '21 at 05:30
  • Please see [mysqli::multi_query](https://www.php.net/manual/en/mysqli.multi-query.php) or execute your queries 1 by 1. – Umair Khan Feb 02 '21 at 05:39

1 Answers1

0

In one statement you can do:

UPDATE product SET pquantity =
    CASE pid
        WHEN 7 THEN '246'
        WHEN 32 THEN '11'
    END
WHERE pid in (7,32);

Sometimes where there is a truly large number of rows, you would use a temporary table:

CREATE TEMPORARY TABLE product_update (pid int, pquantity varchar(10));
INSERT INTO product_update VALUES (7,'246'),(32,'11'),....;
UPDATE product_update JOIN product USING (pid) SET product.pquantity=product_update.pquantity;
DROP TEMPORARY TABLE product_update;
ysth
  • 96,171
  • 6
  • 121
  • 214