根据是否重复输入插入/更新

I have a table with an edit/save button that pulls information from a table view. Once you hit edit, you can edit the Group_ID column. Once you hit save I want it to run an insert query that inserts the SKU and Group_ID into a table (not the view) in my database. However, if the SKU already exists in that table, I want it to run an update query instead.

我有一个具有编辑/保存按钮的表，它从表视图中提取信息。点击编辑后，可以编辑Group_ID列。点击save之后，我希望它运行一个插入查询，该查询将SKU和Group_ID插入到数据库中的表(而不是视图)中。但是，如果SKU已经存在于该表中，我希望它运行一个update查询。

How could I successfully do this? I currently am using AJAX and am bringing the values in properly as far as I know, but I think there may be a problem with my query. Thanks!

我怎么能成功地做到这一点呢?我目前正在使用AJAX，并且就我所知，正在正确地引入值，但是我认为我的查询可能有问题。谢谢!

update-index.php script that is called via AJAX on button click:

索引更新。通过AJAX调用的php脚本点击按钮:

<?php

  $Group_ID = $_POST['Group_ID'];
  $SKU = $_POST['SKU'];

  $host="xxxxxxxx"; 
  $dbName="xxxx"; 
  $dbUser="xxxxxxxxxxxx"; 
  $dbPass="xxxxxxxxxx";

  $pdo = new PDO("sqlsrv:server=".$host.";Database=".$dbName, $dbUser, $dbPass);

  $sql = "IF EXISTS (SELECT SKU FROM SKU_Group_Index WHERE SKU = '$SKU')
          UPDATE SKU = $SKU, Group_ID = $Group_ID
          ELSE
          INSERT INTO SKU_Group_Index (SKU, Group_ID) VALUES (?, ?)";

  $stmt = $pdo->prepare($sql);  
  $result = $stmt->execute();
  echo json_encode($result);


?>

1 个解决方案

#1

You aren't passing any values to $stmt->execute(), and forgot the table name in your UPDATE statement. Try this:

您没有将任何值传递给$stmt->execute()，并且在更新语句中忘记了表名。试试这个:

  $sql = "IF EXISTS (SELECT SKU FROM SKU_Group_Index WHERE SKU = '$SKU')
          UPDATE SKU_Group_Index SET SKU = '$SKU', Group_ID = '$Group_ID'
          ELSE
          INSERT INTO SKU_Group_Index (SKU, Group_ID) VALUES (?, ?)";    
  $stmt = $pdo->prepare($sql);  
  $result = $stmt->execute([$SKU, $Group_ID]);

Also, you must replace those other PHP variables in the query with placeholders as well. It's very dangerous to pass unescaped user data into your database. Try something like this:

此外，还必须用占位符替换查询中的其他PHP变量。将未转义的用户数据传递到数据库中是非常危险的。试试这样:

  $sql = "IF EXISTS (SELECT SKU FROM SKU_Group_Index WHERE SKU = ?)
          UPDATE SKU_Group_Index SET SKU = ?, Group_ID = ?
          ELSE
          INSERT INTO SKU_Group_Index (SKU, Group_ID) VALUES (?, ?)";    
  $stmt = $pdo->prepare($sql);  
  $result = $stmt->execute([$SKU, $SKU, $Group_ID, $SKU, $Group_ID]);

#1