insert ignore

[Yohanne]

Hi coders,

 

i have two fields that sometimes user inputed same value from different field and now i dont want to duplicate it even they are different field.  and what is the best way to avoid duplicate. $a and $b?

for($i = 0; $i<count($a) || $i<count($b); $i++)
    {
    $a_query = mysql_query("INSERT IGNORE INTO code_number(a,b,c) VALUES('$a[$i]','$b[$i]','$c')")or die(mysql_error());	
    }		
return $a_query;

Edited April 29, 2014 by JaysonDotPH

[Jacques1]

What? Are you saying you don't want any value of the two fields appear again in any of the fields?

 

So in this example, the 1 would be a duplicate:?

+---+---+
| a | b |
+---+---+
| 1 | 2 |
| 3 | 1 |
+---+---+

Edited April 29, 2014 by Jacques1

[Psycho]

I was somewhat confused by your post as well. If, as Jacques proposed, the "1" would be considered a duplicate even though it appears in different columns, then INSERT IGNORE will not help with what you are trying to achieve. INSERT IGNORE is used to suppress errors that occur during an insert statement. While it will ignore any errors it can (and is) used for a specific purpose to prevent duplicate records.

 

By adding UNIQUE constraints to your database tables it will automatically enforce the restriction of duplicate records. If you try to run a query that would cause a duplicate, it would generate an error. So, one process that is used is to add those unique constraints and then use INSERT IGNORE when doing the inserts. That way any records that would cause an error because of the unique constraint are ignored. But, you need to be very sure that is what you want - for two reasons.

 

First: the IGNORE will ignore all errors that may occur for that query. You should be very sure that the code to produce your query is not subject to possible errors. For example, you should validate all values for the INSERT programatically before you try to even create the query. Otherwise, you may spend hours trying to debug problems because you are not getting any errors.

 

Second, depending on your situation, the new record may have other additional information that you want updated. In this case, you may want to use INSERT REPLACE or ON DUPLICATE KEY UPDATE

 

Going back to your original statement. If you want to prevent the same value in a single filed/column you can make that field unique in your database. You can also make combinations of fields unique. For example, in the example table above, you could make the combination of field a and field b unique. So, the values of (1, 2) and (3, 1) would be allowed. But, you could not add another record with the same values in the same field. So, you could not add another (1, 2) or (3, 1). But you could add the same values in different fields: (2, 1) and (1, 3) would be allowed.

[Psycho]

I forgot one point. If you will consider the same value in different columns to be a duplicate, then the solution will require you to perform a SELECT query first to detect if the value exists in either column before performing the INSERT. If that really is the case, then it would be interesting to know what these values are and why you have two columns to store the same type of data. This might be better solved by changing the database schema to be normalized.

[Jacques1]

Unfortunately, you can't just do a SELECT and then check for duplicates in your application, because this doesn't work with concurrent requests:

1. User A and user B both try to insert the same new value at the same time.
2. Your application checks the table. Since the value isn't present yet, both users are allowed to pass.
3. Now you have a duplicate despite your check.

But I do agree that you should reconsider your decision and the database design. Does this whole thing really make sense?

[Psycho]

Unfortunately, you can't just do a SELECT and then check for duplicates in your application, because this doesn't work with concurrent requests:

 

Correct, I didn't consider race conditions in my response. But, you can put a write lock the table, do the SELECT, do the INSERT if no duplicate exists, then unlock the table. But, that seems like overkill when the real fix is probably to normalize the db schema.

[Yohanne]

 

What? Are you saying you don't want any value of the two fields appear again in any of the fields?

 

So in this example, the 1 would be a duplicate:?

+---+---+
| a | b |
+---+---+
| 1 | 2 |
| 3 | 1 |
+---+---+

 

yes exactly.  and if i normalize it again its a big re-work since its almost done.

 

before i use

for($i = 0; $i<count($a) || $i<count($b); $i++)
     {
        $sql = mysql_query("SELECT id from code_number WHERE (a = '$a[$i]') || (b = '$b[$i]') || (a = '$b[$i]') || (b = '$a[$i]')");
							
        $row_count = mysql_num_rows($sql);
     }					
	if($row_count === 0)
	   {
		for($i = 0; $i<count($a) || $i<count($b); $i++)
	          {
		    $a_query = mysql_query("INSERT IGNORE INTO code_number(a,b,c) VALUES('$a[$i]','$b[$i]','$c')")or die(mysql_error());	
                  }
	   	    return $a_query;
	   }
	else
	   {
		return false;
	   } 

Edited April 30, 2014 by JaysonDotPH

[Yohanne]

 

What? Are you saying you don't want any value of the two fields appear again in any of the fields?

 

So in this example, the 1 would be a duplicate:?

+---+---+
| a | b |
+---+---+
| 1 | 2 |
| 3 | 1 |
+---+---+

 

yes exactly JACQUES1 and if i normalize it again its a big re-work since its almost done.

 

before i use the following without for loop and it work well.. and since im going for upgrade, i use for loop to makes users work faster. and sad to say code below is not working. 

 

and i use ignore so that i want user to continue and continue to save.  

for($i = 0; $i<count($a) || $i<count($b); $i++)
     {
$sql = mysql_query("SELECT id from code_number WHERE (a = '$a[$i]') || (b = '$b[$i]') || (a = '$b[$i]') || (b = '$a[$i]')");
                            
$row_count = mysql_num_rows($sql);
}                    
    if($row_count === 0)
     {
        for($i = 0; $i<count($a) || $i<count($b); $i++)
     {
         $a_query = mysql_query("INSERT IGNORE INTO code_number(a,b,c) VALUES('$a[$i]','$b[$i]','$c')")or die(mysql_error());    
}
          return $a_query;
     }
    else
     {
        return false;
     } 

[Jacques1]

As we already said, it doesn't work like that.

 

Using INSERT IGNORE requires a UNIQUE constraint, but you cannot have a UNIQUE constraint which regards multiple columns as one big column. And your check doesn't work when there are multiple requests trying to insert the same value at the same time. Those will all be accepted.

 

However: If you just want a quick hack which works most of the time, you might give this a try. But be aware that you may very well end up with duplicate entries. This is a hack, not a correct solution.

Edited April 30, 2014 by Jacques1

[Yohanne]

Thanks JACQUEST and okay let say ignore the ignore

$a_query = mysql_query("INSERT INTO code_number(a,b,c) VALUES('$a[$i]','$b[$i]','$c')")or die(mysql_error());

[Psycho]

 

yes exactly.  and if i normalize it again its a big re-work since its almost done.

 

 

Yeah, no reason to actually fix the underlying problem. Much better to keep putting patchwork band-aids in place that you'll continually need to work around and tweak over time.

 

As stated previously INSERT IGNORE will suppress all errors - not just duplicate checks. Based on the level of code in question, I don't think it makes sense to ignore errors. Plus, the unique constraints would only work for the values in the same columns.

 

If you don't want to fix the real problem, then I would suggest doing a SELECT first to check for existing records. If none are found then do the INSERT. But, as stated previously, you could end up with duplicates due to race conditions - i.e. two people inserting records at the exact same time. It will be pretty rare. But, you could also follow up with the third query to look for duplicates and delete the latter to cover that very small possibility.