Insert/Update Based on Duplicate Entry or Not

Question

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.

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!

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

<?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);


?>

`UPDATE SKU_Group_Index SET SKU = $SKU, Group_ID = $Group_ID WHERE SKU = $SKU` — Ilyes, Feb 23 '17 at 21:13

miken32 · Accepted Answer · 2017-05-31T16:23:52.073

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

  $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:

  $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]);

This worked! Awesome, thanks! Also, how can I effectively use `?` as placeholders in the update part of the query? — Rataiczak24, Feb 23 '17 at 21:46

Insert/Update Based on Duplicate Entry or Not

1 Answers1