If record exists then update or else Insert in php/mysql

Question

The actual scenario is . I have some records in my db. I call the records in edit mode. In this mode the users can 'Add more Rows' (Dynamic rows) and they can update. So which ever records exists should be updated and the newly added rows should be inserted to the table.

I am trying to do a query which updates if 'ID' exists and inserts if it doesn't exists. Here iam using 'ON DUPLICATE KEY UPDATE'. When i tried to run the following query, i could see so many duplicate records being inserted. I guess the duplicate records being generated because of WHILE LOOP. . My query is below. Id is the 'Autoincrement' and 'KEY'. I am not getting any query Errors. DB is Mysql. My php save script is below: Also check the front end..https://jsfiddle.net/euvof3xb/14/

while ($i < $size) {
    $sl= $_POST['sl'][$i];
    $item_id= $_POST['item_id'][$i];
    $item_name= $_POST['item_name'][$i];
    $prod_description=$_POST['prod_description'][$i];
    $prod_description= mysql_real_escape_string($prod_description);
    $item_quantity= $_POST['item_quantity'][$i];
    $item_units= $_POST['item_units'][$i];
    $unitprice= $_POST['unitprice'][$i];
    $total=$_POST['total'][$i];
    $currency_selected=$_POST['currency_change'][$i];
    $total_inr= $_POST['total_inr'][$i];
        $id = $_POST['id'][$i];
    $item_quantity_sup= $_POST['item_quantity_sup'][$i];
    $slab_range= $_POST['slab_range'][$i];
    $item_units_sup= $_POST['item_units_sup'][$i];
    $item_partno= $_POST['item_partno'][$i];
    $ifmain= $_POST['ifmain'][$i];
    $sup_itempartno = $_POST['sup_itempartno'][$i];



    $query = "INSERT INTO comparitive_st_sup (
id, 
tender_id, 
item_id, 
ifmain, 
slno, 
item_name, 
item_partno, 
prod_description, 
sup_itempartno, 
currency, 
slab_range, 
qty, 
total_inr, 
qty_sup, 
item_units, 
item_units_sup, 
unitprice, 
total, 
supplier_name
) 
VALUES (
$id, 
'$tender_id', 
'$item_id',
'$ifmain', 
'$sl', 
'$item_name', 
'$item_partno',
'$prod_description', 
'$sup_itempartno', 
'$currency_selected', 
'$slab_range', 
'$item_quantity', 
'$total_inr', 
'$item_quantity_sup', 
'$item_units', 
'$item_units_sup', 
'$unitprice', 
'$total', 
'$supplier_name2'
)
ON DUPLICATE KEY UPDATE 
ifmain='$ifmain', 
slno = '$sl',
item_name = '$item_name',
item_partno = '$item_partno',
prod_description = '$prod_description',
sup_itempartno = '$sup_itempartno',
currency = '$currency_selected',
slab_range= '$slab_range',
qty = '$item_quantity',
qty_sup = '$item_quantity_sup',
item_units = '$item_units',
item_units_sup = '$item_units_sup',
unitprice = '$unitprice',
total = '$total', 
total_inr='$total_inr'";

        mysql_query($query) or die ("Error in query: $query");
    ++$i;
    }

I have updated the question with the front end html fiddle also. — Sanju Menon, Mar 23 '16 at 04:31
When you said duplicate records, are the same Id? Can you post duplicate records sample? — Mauricio Florez, Mar 23 '16 at 04:42
Possible duplicate of [SQL - IF EXISTS UPDATE ELSE INSERT INTO](http://stackoverflow.com/questions/15383852/sql-if-exists-update-else-insert-into) — adarsh hota, Mar 23 '16 at 06:54

score 1 · Answer 1 · answered Mar 23 '16 at 04:57

One easiest fix is that you want to make your 'id' field in database a primary key. That'd automatically avoid duplicate entries.

Secondly,

You can try the following code for switching between insert and update,

$count=mysql_numrows(mysql_query("SELECT * FROM comparitive_st_sup WHERE id=$id"));

if($count)
{
 $query={UpdateQuery}
}
else
{
 $query={InsertQuery}
}

Additionally mysql functions are being deprecated. Therefore kindly start programming with mysqli functions.

Hope this helped. Thank you.

score 0 · Answer 2 · answered Mar 23 '16 at 07:14

0

I solved this by first deleting the complete rows and again inserting.

answered Mar 23 '16 at 07:14

Sanju Menon

747
1
10
24

If record exists then update or else Insert in php/mysql

2 Answers2