Can I use ON DUPLICATE UPDATE KEY in a PHP for loop

Question

My problem is as follows.. I want to add multiple items to a table at the same time. This is a inventory system and according to a purchase order, items will be added. I want to update 3 tables simultaneously. The first table relates to invoice data and the second table relates to received item details. The third table relates to current stock.

If an item which I am going to add to current stock table already exists it should be updated and if it does not exist it should be added to the current stock table. I use dynamically generated text box to add items to table. But I'm unable to execute ON DUPLICATE UPDATE KEY query in the for loop. My code is as follows..

if (is_array($qty) && ($item)) {
    for ($i = 0; $i < sizeof($qty); $i++) {
        $query2 = "INSERT INTO received_items (po_id,invoice_number,item_name,qty)VALUES($id,'$invoice',$item[$i]',$qty[$i])";
        $query3="INSERT INTO current_stock (item-name,Qty) VALUES('$item[$i]',$qty[$i]) ON DUPLICATE KEY UPDATE item-name='$item[$i]',qty=qty+$qty[$i]";
        $result_1 = mysqli_query($conn, $query2);
        $result_2 =  mysqli_query($conn, $query3);

        if (($result_1)&&($result_2)) {
            echo '<script type="text/javascript">';
            echo 'alert("Sucessfully Updated your Details");';
            echo 'window.location = "../htdocs/dashbd.php";';
            echo '</script>';
        }else{
            echo '<script type="text/javascript">';
            echo 'alert("Failed to Update Your details.Try Again");';
            echo 'window.location = "../htdocs/received_item.php";';
            echo '</script>';
        }
    }
}

Show us the output from a `SHOW CREATE TABLE current_stock` please — RiggsFolly, Apr 26 '17 at 11:50
item name is primary key in current_stock table.... there are only two columns, Item_name and qty. — serverAdmin123, Apr 26 '17 at 14:05
God forbids, how beautifully people gonna SQL Inject this query. — Ali Rasheed, Apr 27 '17 at 08:03

score 0 · Answer 1 · answered Apr 26 '17 at 11:54

0

Your syntax for ON DUPLICATE KEY UPDATE is wrong.

you can either specify col_name = col_name or col_name = VALUES(col_name)

clauses are comma delimited.

answered Apr 26 '17 at 11:54

andrew

9,313
7
30
61

score 0 · Answer 2 · edited May 23 '17 at 12:34

When you enter the ON DUPLICATE part of the syntax, you should not include the key field in the update phase, it already knows the index/key column as it has found it as a duplicate.

Also the UPDATE syntax is just like a normal UPDATE syntax and needs the SET clause

$query3="INSERT INTO current_stock 
                (item-name,Qty) 
         VALUES('$item[$i]',$qty[$i]) 
        ON DUPLICATE KEY 
            UPDATE SET Qty=Qty+$qty[$i]";

You could also do this more safely and efficiently using a prepared and parameterised query like this. This will protect you from SQL Injection Attack and as the query only has to be compiled and optimized once it should also run faster and put less load on your database server.

Also multiple updates shoudl really be done within a transaction to ensure database integrity.

$query2 = "INSERT INTO received_items 
                (po_id,invoice_number,item_name,qty) 
            VALUES(?,?,?,?)";
$query3 = "INSERT INTO current_stock 
                (item-name,Qty) 
           VALUES(?,?) 
           ON DUPLICATE KEY 
             UPDATE SET qty=qty+?";

$stmt2 = $conn->prepare($query2);
$stmt3 = $conn->prepare($query3);

// start a transaction
$conn->begin_transaction();

for ($i = 0; $i < sizeof($qty); $i++) {
    // you may need to check the data types used here????
    $stmt2->bind_param('iisi',$id,$invoice,$item[$i],$qty[$i]);
    $result2 = $stmt2->execute();        

    // you may need to check the data types used here also ????
    $stmt3->bind_param('iii',$id,$qty[$i],$qty[$i]);
    $result3 = $stmt3->execute();        

    if ( !$result2 || !$result3 ) {
        // we got an error, rollback all changes
        $conn->rollback();
        echo '<script type="text/javascript">';
        echo 'alert("Failed to Update Your details.Try Again");';
        echo 'window.location = "../htdocs/received_item.php";';
        echo '</script>';
        exit;
    }
}

// if we get here we did not get an error
// commit the updates
$conn->commit();
echo '<script type="text/javascript">';
echo 'alert("Sucessfully Updated your Details");';
echo 'window.location = "../htdocs/dashbd.php";';
echo '</script>';

I updates this answer to include a transaction and corrected the error or success reporting — RiggsFolly, Apr 27 '17 at 17:01

Can I use ON DUPLICATE UPDATE KEY in a PHP for loop

2 Answers2