Member Avatar for prashant_17
prashant_17 0 Newbie Poster

I have a script for product Invoice which save details of sold product, but it do not update stock quantity when any product sale. Product quantity in stock remain the same which should decrease..

here is the code to save invoice details using for each loop :

<?php

    function saveInvoice( array $data){
            if( !empty( $data ) ){ 
                global $con;

                $count = 0;
                if( isset($data['data'] )){
                    foreach ($data['data'] as $value) {
                        if(!empty($value['product_id'] ))$count++;
                    }
                }

                if($count == 0)throw new Exception( "Please add atleast one product to invoice." );

                return [
                        'success' => true,
                        'uuid' => $uuid,
                        'message' => 'Demo Purpose I have disabled invoice save, Please download script and check in your local machine..'
                    ];

                // escape variables for security
                if( !empty( $data)){
                    $client_id = mysqli_real_escape_string( $con, trim( $data['client_id'] ) );
                    $invoice_total = mysqli_real_escape_string( $con, trim( $data['invoice_total'] ) );
                    $invoice_subtotal = mysqli_real_escape_string( $con, trim( $data['invoice_subtotal'] ) );
                    $tax = mysqli_real_escape_string( $con, trim( $data['tax'] ) );
                    $amount_paid = mysqli_real_escape_string( $con, trim( $data['amount_paid'] ) );
                    $amount_due = mysqli_real_escape_string( $con, trim( $data['amount_due'] ) );
                    $notes = mysqli_real_escape_string( $con, trim( $data['notes'] ) );

                    $id = mysqli_real_escape_string( $con, trim( $data['id'] ) );

                    if(empty($id)){
                        $uuid = uniqid();
                        $query = "INSERT INTO invoices (`id`, `client_id`,  `invoice_total`, `invoice_subtotal`, `tax`,
                                `amount_paid`, `amount_due`, `notes`, `created`, `uuid`)
                                VALUES (NULL, '$client_id',  '$invoice_total', '$invoice_subtotal', '$tax', '$amount_paid', '$amount_due', '$notes',
                                CURRENT_TIMESTAMP, '$uuid')";

                    }else{
                        $uuid = $data['uuid'];
                        $query = "UPDATE `invoices` SET `client_id` = '$client_id', `invoice_total` ='$invoice_total',`invoice_subtotal` = '$invoice_subtotal',
                                `tax` = '$tax', `amount_paid` = '$amount_paid', `amount_due` = '$amount_due', `notes` = '$notes', `updated` = CURRENT_TIMESTAMP
                                WHERE `id` = $id";
                    }
                    if(!mysqli_query($con, $query)){
                        throw new Exception(  mysqli_error($con) );
                    }else{
                        if(empty($id))$id = mysqli_insert_id($con);
                    }

                    if( isset( $data['data']) && !empty( $data['data'] )){
                        saveInvoiceDetail( $data['data'], $id );
                    }
                    return [
                        'success' => true,
                        'uuid' => $uuid,
                        'message' => 'Invoice Saved Successfully.'
                    ];
                }else{
                    throw new Exception( "Please check, some of the required fileds missing" );
                }
            } else{
                throw new Exception( "Please check, some of the required fileds missing" );
            }
        }

    function saveInvoiceDetail(array $invoice_details, $invoice_id = ''){ 
        global $con;
        $deleteQuery = "DELETE FROM invoice_details WHERE invoice_id = $invoice_id";
        mysqli_query($con, $deleteQuery);

        foreach ($invoice_details as $invoice_detail){
            $product_id = mysqli_real_escape_string( $con, trim( $invoice_detail['product_id'] ) );
            $productName = mysqli_real_escape_string( $con, trim( $invoice_detail['product_name'] ) );
            $quantity = mysqli_real_escape_string( $con, trim( $invoice_detail['quantity'] ) );
            $price = mysqli_real_escape_string( $con, trim( $invoice_detail['total'] ) );

            $query = "INSERT INTO invoice_details (`id`, `invoice_id`, `product_id`, product_name, `quantity`, `price`)
                    VALUES (NULL, '$invoice_id', '$product_id', '$productName', '$quantity', '$price')";
            mysqli_query($con, $query);
        }

    }

we have product table with is, productname, quantityInStock row. this script saving details of product in Invoice_details table :

function saveInvoiceDetail(array $invoice_details, $invoice_id = ''){ 
    global $con;
    $deleteQuery = "DELETE FROM invoice_details WHERE invoice_id = $invoice_id";
    mysqli_query($con, $deleteQuery);

    foreach ($invoice_details as $invoice_detail){
        $product_id = mysqli_real_escape_string( $con, trim( $invoice_detail['product_id'] ) );
        $productName = mysqli_real_escape_string( $con, trim( $invoice_detail['product_name'] ) );
        $quantity = mysqli_real_escape_string( $con, trim( $invoice_detail['quantity'] ) );
        $price = mysqli_real_escape_string( $con, trim( $invoice_detail['total'] ) );

        $query = "INSERT INTO invoice_details (`id`, `invoice_id`, `product_id`, product_name, `quantity`, `price`)
                VALUES (NULL, '$invoice_id', '$product_id', '$productName', '$quantity', '$price')";
        mysqli_query($con, $query);
    }
  • 1 Contributor
  • 0 Replies
  • 266 Views

Dani AI

Generated

— quick analysis of the posted functions: the invoice routine inserts invoice_details but never touches the products table, and there is an early demo return that short‑circuits the rest of the function (so no DB updates run). The current delete-then-insert pattern for details can work for edits, but it must be wrapped in a transaction and coupled with an atomic stock update per product to avoid negative stock and race conditions.

Recommended, safe flow (high level):

  • Start a transaction (InnoDB required).
  • Insert or update the invoice and obtain its invoice_id.
  • For each line: atomically decrement stock and verify the update succeeded; then insert the invoice_detail row.
  • If any step fails, rollback; otherwise commit.
    Use prepared statements (not manual escaping) and check affected rows on the stock UPDATE to detect insufficient stock or concurrent conflicts. As an alternative when the previous stock value is needed, SELECT ... FOR UPDATE can be used to lock the row inside the same transaction.

Example pattern (focus on the foreach / stock-update part; assume $invoice_id is already set and $details is the posted array):

<?php
mysqli_begin_transaction($con);

$updateStock = mysqli_prepare($con,
  "UPDATE products
   SET quantityInStock = quantityInStock - ?
   WHERE id = ? AND quantityInStock >= ?"
);

$insertDetail = mysqli_prepare($con,
  "INSERT INTO invoice_details (invoice_id, product_id, product_name, quantity, price)
   VALUES (?, ?, ?, ?, ?)"
);

foreach ($details as $line) {
    $pid  = (int)$line['product_id'];
    $qty  = (int)$line['quantity'];
    $name = $line['product_name'];
    $price= (float)$line['total'];

    mysqli_stmt_bind_param($updateStock, "iii", $qty, $pid, $qty);
    mysqli_stmt_execute($updateStock);
    if (mysqli_stmt_affected_rows($updateStock) === 0) {
        mysqli_rollback($con);
        throw new Exception("Insufficient stock for product ID {$pid}");
    }

    mysqli_stmt_bind_param($insertDetail, "iisid", $invoice_id, $pid, $name, $qty, $price);
    if (!mysqli_stmt_execute($insertDetail)) {
        mysqli_rollback($con);
        throw new Exception("Failed to save invoice detail for product ID {$pid}");
    }
}

mysqli_commit($con);
?>

Notes and cautions:

  • Remove the demo early return so the save logic actually runs.
  • Ensure the products table uses InnoDB and quantityInStock is an integer column.
  • Log DB errors, close/free statements, and write tests that simulate concurrent sales.
  • Consider database-level safeguards (CHECK constraints, foreign keys) to keep data consistent.
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.