php - 在MySQL中使用多个唯一列插入更新

我有一个带有(id, stock_code, batch_code, qty)的表。

id是主键和自动递增值。

stock_code和batch_code是唯一的组合。现在，当stock_code和batch_code重复时，我想在插入查询中更新

例

id     stock_code     batch_code      qty
1      r001           b001            100
2      roo1           b002            100    //it should be insert
3      roo1           boo1            120    //it should be update in first row
4      roo2           boo1            130    //it should be insert
5      roo1           boo2            289    //it should be update in second row

最佳答案

$stockcode = "some value";
$batchcode="some value";
$qty = some value;
$con=mysqli_connect("localhost","my_user","my_password","my_db");

$query22 = "select * from table where stock_code= '".$stockcode."' and batch_code='".$batchcode."'";

$result22 = mysqli_query($con,$query22) or die (mysqli_error());

$num22 = mysqli_num_rows($con,$result22);

if($num22 > 0) {
$query222 = "update table set qty='".$qty."' where stock_code= '".$stockcode."' and batch_code='".$batchcode."'";

$result222 = mysqli_query($con,$query222) or die (mysqli_error($con));
 }
else {
$query222 = "insert into table(stock_code, batch_code, qty) values('".$stockcode."','".$batchcode."','".$qty."') ";

$result222 = mysqli_query($con,$query222) or die (mysqli_error($con));
}