How can I avoid multiple deduction from stocks in my SQL db in php while loop?
Tags: php, sql
Published: 2022-08-12 20:28:00

Please 🙏 guys, I have been having issues when I have alot of people trying to checkout after placing orders on my site. 
I made use of while loop to ensure that the quantity ordered deducts from available quantity in my SQL database, but instead of deducting as planned, it deducts excess.
Here is my code below....
Am I doing it the wrong way?

```
<?php
//Database connection
include("db/config.php");

//Session to get user id
$user_id=$_SESSION['id'];
$statuss='added';

   $value2='';

    //Query to fetch last inserted invoice number and create a new invoice number 
    $query = "SELECT invoice_no from invoice order by invoice_no DESC LIMIT 1";
    $stmt = $conn->query($query);
    if(mysqli_num_rows($stmt) > 0) {
        if ($row = mysqli_fetch_assoc($stmt)) {
            $value2 = $row['invoice_no'];
            $value2 = substr($value2, 10, 13);//separating numeric part
            $value2 = $value2 + 1;//Incrementing numeric part
            $value2 = "ARW/19-20/" . sprintf('%03s', $value2);//concatenating incremented value
            $value = $value2; 
        }
    } 
    else {
        $value2 = "ARW/19-20/001";
        $value = $value2;
    }
    // echo $value;
if(isset($_POST['check_out'])){
           
   //Main code
 
//Get all input values
$name=mysqli_real_escape_string($conn,$_POST['name']);
    $phone=mysqli_real_escape_string($conn,$_POST['phone']);
    $address=mysqli_real_escape_string($conn,$_POST['address']);
    $payment_method=mysqli_real_escape_string($conn,$_POST['payment_mtd']);


 

        $added='added';
//Get session name     $user_name=$_SESSION['name'];

//Query to update order operation table
        $sql="UPDATE order_operation SET name='$name', user_name='$user_name', phone='$phone', address='$address',payment='$payment_method' WHERE user_id=$user_id AND status='$added'";   
        if($conn->query($sql) === TRUE){
//Declare variables       
$before='added';
$status='completed';

//Update order operation with created invoice number

$sql="UPDATE order_operation SET status=?, invoice_no=? WHERE user_id='$user_id' AND status='$before'";
            $stmt=$conn->prepare($sql); 
            $stmt->bind_param('ss',$status,$value);
            if($stmt->execute()){
                $sql="INSERT INTO invoice(invoice_no,user_id) VALUES('$value','$user_id')";
                $conn->query($sql);
                    //Deduct from stock
                    $sql = "SELECT * FROM `order_operation` WHERE invoice_no='$value'";
                    $sql_run = mysqli_query($conn, $sql);
                
                    while ($row = mysqli_fetch_array($sql_run)) {
 //Deduct from stock since the order id's meet the requirements                                         $deduct=$row['stock_quantity'] - $row['quantity'];
                            $stock_id=$row['stock_id'];
                           $connector= "UPDATE stocks SET quantity =  '$deduct' WHERE id = $stock_id" ;
                           if($conn->query($connector) === TRUE){

                               
                                $_SESSION['customer_name']=$name;   
                                $_SESSION['phone']=$phone;
                                $_SESSION['address']=$address;
                                $_SESSION['payment']=$payment_method;
                                $_SESSION['invoice_no']=$row['invoice_no'];
                                header('location:order_invoice.php');
                               
                           }else{
                            echo $conn->error;
                           }
                 
                        
                        
                        }
                        //End of deduct from stock

                    $conn->close();
                
                 }else{
                    die($conn->error);

                 }
             }else{
                
                 die($conn->error);
                 
             }
         
    
               $stmt->close();
               $conn->close();
     
}


?>