How to Check If a Value Already Exists to Avoid Duplicates

How to check if a value already exists to avoid duplicates?

If you don't want to have duplicates you can do following:

• add uniqueness constraint
• use "REPLACE" or "INSERT ... ON DUPLICATE KEY UPDATE" syntax

If multiple users can insert data to DB, method suggested by @Jeremy Ruten, can lead to an error: after you performed a check someone can insert similar data to the table.

check if duplicate value is being added to mysql table

You can specify a UNIQUE index on the item_code field.

CREATE UNIQUE INDEX item_code_unique
ON inventory(item_code);

You can then use a try-catch block to catch any error from inserting duplicates.

try:
    cursor.execute("INSERT INTO ....")
except MySQLdb.IntegrityError:
    print("Duplicate entry")

See also: How to avoid duplicate entries in a MySQL database without throwing an error

Using MySQL UNIQUE Index To Prevent Duplicates

How to check if a value already exists to avoid duplicates?If it exist then do nothing otherwise insert record into database

Two strategies:

1. Let the database do this task. Alter your table so the field you want to be unique is actually a unique index. When you want to insert the data, and it is a duplicate, you'll receive an error which can be used to decide what to do next. Or you might use an ON DUPLICATE KEY if you want to do another table operation instead, if needed.
2. Query the table to find out if there the id is already present. If not, generate your insert.

Example:

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
$result = $mysqli->query("SELECT id FROM yourtable");
if ($result->num_rows > 0) {
    /* do your insert */
}
$result->close();

I prefer letting the database do the job.

Update:

On request here are two examples on how to add an unique index to your table. The SQL looks like this:

CREATE UNIQUE INDEX your index_name
ON your_table (your_id_field);

When inserting data, mysql will throw an error if the key already exists. To work with this error, do something like this:

$SQL = 'INSERT INTO yourtable (yourfield1, yourfield2) 
        VALUES ( 1, "Hello")';    
$result = $mysqli->query($SQL);

if (!mysqli_query($link, $SQL)) {
    if(mysqli_errno($link) == 1062) {
         /* duplicate ... whatever you want in this case */
    }
}

You don't need all this error handling if you don't want to do anything in this situation of course.

How to check if sql values already exist in a table to prevent duplicates?

I assume the code that inserts values is together with the code that prints them? Which means your insertion code is running each time you refresh, inserting the values again. You shouldn't be running the insertion code except when necessary.

However, to prevent duplicates when inserting data, you either need to do a check before inserting a value again, or you need to modify your query to prevent duplicates. Read these, they will help.

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html <- How to write a query that will insert new records and fall back on an update if the record already exists.

http://dev.mysql.com/doc/refman/5.0/en/replace.html <- Similar result, different method.

These methods require that you have your primary key set to be unique, and that the components of the primary key are part of your insert query. If that is not possible, and your primary key is separate (such as an auto-increment), then your only choice is to check for the entry before creating it again.

Add duplicates to a set or check if the elements exists then add

It's faster to add without checking. Every time you add an item to a set it needs to check that the item isn't there - Checking before is not saving you any time if the item is there, but it is wasting time if it isn't there as it will be checked twice.

Insert record if all values do not exist in a row

If you are fine with implementing a general mechanism to avoid duplicates on columns column_a/b/c, you can simply add a composite unique constraint on both columns, like

ALTER TABLE mytable 
ADD CONSTRAINT constr_ID UNIQUE (column_a, column_b, column_c);

If anything operation happens on the table that would generate duplicates, MySQL will raise a constraint violation error. You can ignore the error with the ON DUPLICATE KEY UPDATE option :

INSERT INTO mytable(column_a, column_b, column_c)
VALUES(value_a, value_b, value_c)
ON DUPLICATE KEY UPDATE column_a = column_a;

In this demo on DB Fiddle, we are inserting 3 records with 2 duplicates, and we end up with 2 records in the table, as expected.

---

On the other hand, if you want to restrict the check on duplicate to just one query, and/or if you want to avoid wasting autoincrement sequences on duplicate keys, then you can turn to an INSERT ... SELECT statement, with a WHERE NOT EXISTS condition that does the duplicate check :

INSERT INTO mytable(column_a, column_b, column_c)
SELECT src.*
FROM (SELECT value_a column_a, value_b column_b, value_c column_c) src
WHERE NOT EXISTS (
    SELECT 1 
    FROM mytable
    WHERE 
        column_a = src.column_a 
        AND column_b = src.column_b 
        AND column_c = src.column_c
);

If you attempt to insert a duplicate, the query will do nothing (and will not generate an error or warning).

Demo on DB Fiddle.

Angular: How to avoid duplicate entries in localstorage

In your activeskill function... you're pushing the element (which is passed to the function) in value array without checking if it was already there... hence the duplication; to resolve this, check to see if the value already exists in the value array before you push;

relevant TS:

myFavArray: PeriodicElement[] = [];

  constructor() {
    if (localStorage.getItem('fav') == '' || localStorage.getItem('fav') == null) {
    } else {
      this.myFavArray = JSON.parse(localStorage.getItem('fav'));
      console.log('from localStorage', this.myFavArray);
    }
  }
  activeSkill(element) {
    let checkExists: boolean = false;
    for (var i = 0; i < this.myFavArray.length; i++) {
      if (element.position == this.myFavArray[i].position) {
        checkExists = true;
      }
    }
    if (checkExists == false) {
      this.myFavArray.push(element);
    }
    localStorage.setItem('fav', JSON.stringify(this.myFavArray));
  }

relevant HTML:

<table mat-table [dataSource]="dataSource" class="mat-elevation-z8">

    <!--- Note that these columns can be defined in any order.
        The actual rendered columns are set as a property on the row definition" -->

    <!-- Position Column -->
    <ng-container matColumnDef="position">
        <th mat-header-cell *matHeaderCellDef> No. </th>
        <td mat-cell *matCellDef="let element">
            <button  (click)="activeSkill(element)" mat-icon-button>
    <mat-icon aria-label="Heart">favorite</mat-icon>
 </button>

      {{element.position}} </td>
  </ng-container>

  <!-- Name Column -->
  <ng-container matColumnDef="name">
    <th mat-header-cell *matHeaderCellDef> Name </th>
    <td mat-cell *matCellDef="let element"> {{element.name}} </td>
  </ng-container>

  <!-- Weight Column -->
  <ng-container matColumnDef="weight">
    <th mat-header-cell *matHeaderCellDef> Weight </th>
    <td mat-cell *matCellDef="let element"> {{element.weight}} </td>
  </ng-container>

  <!-- Symbol Column -->
  <ng-container matColumnDef="symbol">
    <th mat-header-cell *matHeaderCellDef> Symbol </th>
    <td mat-cell *matCellDef="let element"> {{element.symbol}} </td>
  </ng-container>

  <tr mat-header-row *matHeaderRowDef="displayedColumns"></tr>
  <tr mat-row *matRowDef="let row; columns: displayedColumns;"></tr>
</table>

minimal verifiable complete stackblitz here

Do Not Duplicate VALUE if already exist on MySQL

The mysql_* function are all deprecated now, and should NEVER be used. change your code to do something like the following:

//Set up a PDO connection to MySQL
$host = 'host_name';
$dbname = 'database_name';
$user = 'user_name';
$pass = 'user_pass';
try
{
    $DB = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass); 
}
catch(PDOException $e)
{  
    echo $e->getMessage();  
}

//Determine whether the appropriate values have been passed.
if(isset($_POST['Name']))
{
    $name = $_POST['Name'];
}
else
{
    echo "You must provide a name!";
        exit; //This may not be what you want to do here, it's an example action
}

if(isset($_POST['LastName']))
{
        $name = $_POST['LastName'];
}
else
{
    echo "You must provide a last name!";
        exit; //This may not be what you want to do here, it's an example action
}

if(isset($_POST['Phone']))
{
        $name = $_POST['Phone'];
}
else
{
    echo "You must provide a phone number!";
        exit; //This may not be what you want to do here, it's an example action
}

//Set up the query using anonymous values
$sql="INSERT INTO people (Name, LastName, Phone) VALUES ('?','?','?')";
$sth = $DB->prepare($sql);

try
{
        //Attempt to execute the insert statement
        $sth->execute(array($_POST[Name], $_POST[LastName], $_POST[Phone]));
        echo "Record Added";

}
catch(PDOException $e)
{
        //If the insert failed, then you can handle the error, and determine
        //what further steps need to be taken.
        echo "Record Not Added";
}

Here's another question with a similar setting, that may also be useful to you:

https://stackoverflow.com/a/10414922/1507210

Should you check for a duplicate before inserting into a set

As per the docs:

public boolean add(E e)

Adds the specified element to this set if it is not already present. More formally, adds the specified element e to this set if this set contains no element e2 such that (e==null ? e2==null : e.equals(e2)). If this set already contains the element, the call leaves the set unchanged and returns false.

So the add() method already returns you a true or a false. So you don't need to do the additional check.

---

Related Topics