Do Not Insert to Database when Duplicate Record is Input

Question

Right now, I have a dialog box that pops up when an Add button is clicked. There are 2 inputs inside that will be inserted into the database upon submit. There is a dropdown list and an input box. I do not want the submit on the dialog box to be able to work if a value that is already existing in the database, is entered into the input box. So basically, I do not want there to be any duplicate records in the Supp_ID columns. How can I do this? Here is what I have so far.

Dialog Form:

<div id="dialog-form" title="Add Supplier ID">
  <p class="validateTips">All form fields are required.</p>

<!-- Dialog box displayed after add row button is clicked -->
  <form >
    <fieldset>
      <label for="mr_id">MR_ID</label>
      <select name="mr_id" id="mr_id_dialog" class="text ui-widget-content ui-corner-all" value="300">
          <?php foreach($user1->fetchAll() as $user2) { ?>
            <option>
                <?php echo $user2['MR_ID'];?>
            </option>
        <?php } ?>
      </select><br><br>
      <label for="supplier_id">Supplier ID</label>
      <input type="text" name="supp_id" id="supplier_id" class="text ui-widget-content ui-corner-all" value="99">

      <!-- Allow form submission with keyboard without duplicating the dialog button -->
      <input type="submit" id="submit" tabindex="-1" style="position:absolute; top:-1000px">
    </fieldset>
  </form>
</div>

JavaScript:

$("document").ready(function() {
        $('#submit').submit(function() {
                processDetails();
                return false;
        });
});

function processDetails() {
        var errors = '';

        // Validate Supp ID
        var supplier = $("#supplier_id [name='supp_id']").val();
        if (supplier == "null" || supplier == "") { // check for empty value
                errors += ' - Please enter a different Supplier ID\n';
        }
        // MORE FORM VALIDATIONS
        if (errors) {
                errors = 'The following errors occurred:\n' + errors;
                alert(errors);
                return false;
        } else {
                // Submit form via Ajax and then reset the form
                $("#submit").ajaxSubmit({success:showResult}).resetForm();
                return false;
        }
}

function showResult(data) {
        if (data == 'save_failed') {
                alert('ERROR. Your input was not saved.');
                return false;
        } else if (data == 'save_failed_duplicate') {
                alert('ERROR. Input data already exists.');
                return false;
        } else {
                alert('SUCCESS. Your input data has been saved.');
                return false;
        }
}

Insert.php

<?php
$MR_ID = $_POST['MR_ID'];
$Supp_ID = $_POST['Supp_ID'];

  $host="xxxxxxxx"; 
  $dbName="xxxx"; 
  $dbUser="xxxxxxxxxxx"; 
  $dbPass="xxxxxxxxx";

  $pdo = new PDO("sqlsrv:server=".$host.";Database=".$dbName, $dbUser, $dbPass);
  $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );


$check_sql = "SELECT Supp_ID FROM Stage_Rebate_Index WHERE Supp_ID = '$Supp_ID'";
$check_sql_query = sqlsrv_query($check_sql, $dbh);
if (sqlsrv_num_rows($check_sql_query) > 0) {
        echo "save_failed_duplicate";
        @sqlsrv_close($dbh);
        return;
} else {
    if (sqlsrv_num_rows($check_sql_query) == 0) {
        $sql = "INSERT INTO Stage_Rebate_Index (MR_ID, Supp_ID) VALUES (?, '$Supp_ID')";
        if (@sqlsrv_query($sql, $dbh)) {
                echo "success";
                @sqlsrv_close($dbh);
                return;
        } else {
                echo "save_failed";
                @sqlsrv_close($dbh);
                return;
        }
    }
}

  $stmt = $pdo->prepare($sql);
  $result = $stmt->execute(array($MR_ID, $Supp_ID));
  echo json_encode($result);


?>

make **Supp_ID columns** unique in database and use **upsert query** to avoid duplicate values. — Aabir Hussain, Dec 21 '16 at 18:54

score 1 · Answer 1 · edited May 23 '17 at 11:53

1

I always use ON DUPLICATE KEY UPDATE in case I want to update a timestamp or something.

$sql = "INSERT INTO Stage_Rebate_Index (MR_ID, Supp_ID) VALUES (?, '$Supp_ID') ON DUPLICATE KEY UPDATE `MR_ID` = `MR_ID`";

Basically, it attempts to insert the record, but if the key exists, it updates whatever data you need. In the above example, it simply updates the MR_ID with the original MR_ID.

Here is a previous question similar to this one: Link

Here is the link the Mysql manual: Link

edited May 23 '17 at 11:53

Community

1
1

answered Dec 21 '16 at 19:20

john

1,330
3
20
34

I would not want to update though...if it is a duplicate, I would just want it to return an error – Rataiczak24 Dec 21 '16 at 19:26
1

Then you need to add a unique index to the column you're trying to make unique. It will result in an error if the unique value already exists. – john Dec 21 '16 at 19:33

score 0 · Answer 2 · answered Dec 21 '16 at 18:55

Use the IGNORE keyword in your SQL statement.

$sql = "INSERT IGNORE INTO Stage_Rebate_Index (MR_ID, Supp_ID) VALUES (?, '$Supp_ID')";

You will need to modify your response to check the number of rows affected instead of whether the query failed.

See: http://php.net/manual/en/pdostatement.rowcount.php

Do Not Insert to Database when Duplicate Record is Input

2 Answers2