update or insert multiple records if not exists in a table in mysql database

Question

There is a table named "inventory_item" in a mysql database. "id", "product_category_id" and "quantity" are columns of the table. "id" is the primary key and auto generates when a record is inserted.

when the submit button is clicked, which was created to insert multiple records to the table using php all the data of product_category_ids and their quantities can be collected in foreach loop.

so I need to insert those multiple records at the same time and should only update quantity if "product_category_id" is already exist in the table without inserting as a new record.

the code block is here..

foreach ($dataArray as $value) {

    $pcid = $value["pcid"];
    $quantity = $value["quantity"];

    $sql = "SELECT * FROM inventory_item WHERE product_category_id='$pcid'";
    $result = $conn->query($sql);
    $row = mysqli_fetch_assoc($result);
    $currentQuantity = $row['quantity'];

    $newQuantity = $quantity + $currentQuantity;

    if ($result == true) {

        $sql2 = "IF EXISTS (SELECT * FROM inventory_item WHERE product_category_id='$pcid') UPDATE inventory_item SET quantity=$newQuantity WHERE product_category_id=’$pcid’ ELSE INSERT INTO inventory_item (product_category_id, quantity) VALUES ('$pcid', '$quantity')";
        $result = $conn->query($sql2);
    } else {
        echo 'error';
    }

}

Possible duplicate of mysql update or insert multiple records if not already exists in a table — Bernd Buffen
– Bernd Buffen, Commented Dec 3, 2016 at 12:50

Fabio · Accepted Answer · 2016-12-03 12:55:59Z

1

$result = $conn->query($sql);

Will always return a resource so this will always be true, you might want to check for number of rows returned though. If there are no row so insert otherwise update

if(mysqli_num_row($result) == 0) {
    //no rows insert
} else { 
    //row exist do whatever you need
}

Or in object oriented

if($result->num_rows == 0) {
     //row doesn't exist

answered Dec 3, 2016 at 12:55

Fabio

23.5k12 gold badges57 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

update or insert multiple records if not exists in a table in mysql database

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related