Check for duplicates before inserting

Question

Before inserting into the database, I'm using the following code to check for duplicates. To me, a duplicate is only considered a duplicate when name, description, price, city, and enddate match.

foreach($states_to_add as $item) {
    $dupesql = "SELECT 
                    COUNT(*) 
                FROM 
                    table 
                WHERE 
                    (   
                        name = '$name' 
                        AND description = '$description' 
                        AND manufacturer = '$manufacturer' 
                        AND city ='$city' 
                        AND price = '$price' 
                        AND enddate = '$end_date'
                    )";

    $duperaw = mysql_query($dupesql);

    if($duperaw > 0) {
        echo nl2br("$name already exists in $city \n");
    } 
    else {
        $sql = "INSERT INTO table (..... (here go the values to be inserted)
        ....

Each value is defined prior to running through this checking, my result always comes back as item already exists. I dumped "dupesql" and copy/pasted the command into phpmyadmin which comes back with count 0.

Answer 1

You want to do the following:

$dupesql = "SELECT * FROM table where (name = '$name' AND description = '$description' AND manufacturer = '$manufacturer' AND city ='$city' AND price = '$price' AND enddate = '$end_date')";

$duperaw = mysql_query($dupesql);

if (mysql_num_rows($duperaw) > 0) {
  //your code ...
}

See Here for more information.

Answer 2

As I see it your question can be broken down into 2 parts. Why is my PHP code not working? I don't know, don't know much PHP and other people seem to have just answered that :-). The second question is how can I check for duplicates? You're checking for duplicates the completely wrong way.

Create a unique index / primary key on your table. Then when you try to insert the DB will throw an error if there's a duplicate. Catch the error and deal with it how you want. Counting the number of records is definitely the wrong way to go and will be a significant detriment to the speed of your code.

Answer 3

You can still run into race conditions where 2 users try to insert dups at the same time, checking for dups using a select statement simultaneously gives both users the go ahead to insert their records. I prefer to set unique index on the DB and then catch the error that bubbles up from the DB.

Answer 4

In short you need to do your checking through PHP and you also need to add a composite unique constraint in your MySQL table to get the best of both worlds.

it would be good if we broke down your question into two separate questions.

1- How do I check duplicates using PHP and notify about the user before executing query? 2- How do I specify a composite unique constraint in MySQL?

First, you need a simple PHP function to check whether this record exist in the DB or not, like the one below:

function is_exist($table, $data){
    $sql = "SELECT * FROM `" . $table . "` WHERE ";

    foreach ($data as $key => $val) :
    $sql .= "`$key`='" . $val . "' AND ";
    endforeach;

    $sql = substr($sql,0, -5);

    $result = $mysql_query($sql);
    $count = $mysql_num_rows($result);

    return ($count > 0) ? true: false;
}   

You should call your PHP function like this way below:

    $data = array('column1'=>$_POST['value'],'column2'=>$_POST['value'], ...);

    if(is_exist($data)){
      // Print your error message
    }else{
     // Run your insert query
    }

This way you can prevent duplicates before going to the MySql Database, but in order to have a duplicate free database, you need to add a composite unique constraint in your MySQL table.

This simple SQL command can do the trick:

alter table `tablename` add unique index(name, description, manufacturer, city, price, enddate);

I hope that helps, if I mess anything please burden me.

Answer 5

Try Following

  INSERT IGNORE INTO person_tbl (last_name, first_name) 
  -> VALUES( 'Jay', 'Thomas');

Answer 6

Try this one:

foreach($states_to_add as $item) {
    $dupesql = "SELECT 
                    name 
                FROM 
                    table 
                WHERE 
                    (name = '$name' 
                        AND description = '$description' 
                        AND manufacturer = '$manufacturer' 
                        AND city ='$city'
                        AND price = '$price' 
                        AND enddate = '$end_date'
                    )";

    $duperaw = mysql_query($dupesql);

    if( mysql_num_rows($duperaw) ) {
        echo nl2br("$name already exists in $city \n");
    } 
    else {
        $sql = "INSERT INTO table (..... (here go the values to be inserted)

Answer 7

You don't need to check for uniqueness in PHP, you can just do the whole test in MySQL:

INSERT INTO table1 (name, description, ......)
  SELECT name1, description1, manufacturer1, city1, price1, enddate1
  FROM (SELECT * FROM ( 
    SELECT 
      @name:= '$name' as name1
      ,@description:= '$description' as description1
      ,.....
    FROM DUAL as d1
    LEFT JOIN table1 t1 
           ON (t1.name = d1.name1 
          AND t1.description = d1.description1
          AND ......)
    WHERE t1.name IS NULL) s1) s2

This will only insert the values if they pass the uniqueness test.