在重复键更新语法php / mysql错误

I am trying to do a query which updates if 'ID' exists and inserts if it doesn't exists. When the tried to run the following query, i could see so many duplicate records being inserted. I dont know what i am doing wrong. My query is below. Id is the 'Autoincrement' and 'KEY'. I am not getting any query Errors. DB is Mysql

我正在尝试进行查询,如果“ID”存在则更新,如果不存在则插入。当试图运行以下查询时,我可以看到插入了这么多重复记录。我不知道我做错了什么。我的查询如下。 Id是'Autoincrement'和'KEY'。我没有得到任何查询错误。 DB是Mysql

    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;
    }

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 some 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.

实际情况是。我的数据库中有一些记录。我以编辑模式调用记录。在此模式下,用户可以添加一些行(动态行),并且可以更新。因此应该更新存在的记录,并且应该将新添加的行插入到表中。

3 个解决方案

#1

An INSERT ON DUPLICATE KEY UPDATE query will Insert before it updates so if your unique key is auto-incrementing, it will first insert a new record then look to update but because it is never the same, it will never update.

INSERT ON DUPLICATE KEY UPDATE查询将在更新之前插入,因此如果您的唯一键是自动递增,它将首先插入新记录然后查找更新,但因为它永远不会相同,所以它永远不会更新。

#2

You forgot the INTO keyword:

你忘了INTO关键字:

$query = "INSERT INTO comparitive_st_sup (id, tender_id) 
                             VALUES ( $id, '$tender_id')
          ON DUPLICATE KEY UPDATE tender_id='$tender_id'";

#3

Have you tried INSERT INGORE? It will insert a row if it doesn't exist. If it does exist, it will skip.

你有没有尝试INSERT INGORE?如果它不存在,它将插入一行。如果它确实存在,它将跳过。

#1

#2