Check for duplicate entries - mysql insert

[thaidomizil]

Hello,

 

i am inserting some form data into my mysql db, i happen to get some duplicates so i want to check first if the entry exists already before i insert.

 

my current code:

 

<?php

if(isset($_POST['submit']))  { ?>
<?php 
if (strlen($_POST['code']) == 19 && substr($_POST['code'],0,7) == '5541258')  { 
     mysql_query("INSERT INTO table(code,secret,ip,date) VALUES('$_POST[code]','$_POST[secret]','$_SERVER[REMOTE_ADDR]',CURDATE())");
Print "<font color='green'>The code will be checked now</font>"; 
} else { 
    
Print "<font color='red'>The code is invalid</font>"; 
}
?><?php } ?>

 

I would like to use the value 'code' to check if the entry exists, that one is unique for each entry. How would i do that ?

 

Thanks !

[AyKay47]

this method will work.. however I am drawing a blank for any other methods atm..

 

if(isset($_POST['submit'])){
   if (strlen($_POST['code']) == 19 && substr($_POST['code'],0,7) == '5541258'){
      $code = $_POST['code'];
      $select_query = mysql_query("SELECT * FROM table WHERE code = '$code'");
      if(mysql_num_rows($select_query) == 0){
         $remote_addr = $_SERVER['REMOTE_ADDR'];
         $secret = $_POST['secret'];
         mysql_query("INSERT INTO table(code,secret,ip,date) VALUES('$code','$secret','$remote_addr',CURDATE())");
      }
   }
}

[The Little Guy]

You could do an "INSERT IGNORE" which would ignore duplicate rows.

 

So, you would need to make the column "code" as either a primary key or a unique key.

 

<?php
$sql = null;
if(isset($_POST['submit'])){
if(strlen($_POST['code']) == 19 && substr($_POST['code'],0,7) == '5541258')  { 
	$code = mysql_real_escape_string($_POST['code']);
	$secret = mysql_real_escape_string($_POST['secret']);
	$addr = mysql_real_escape_string($_SERVER['REMOTE_ADDR']);
	$sql = mysql_query("INSERT IGNORE INTO table(code,secret,ip,date) VALUES('$code','$secret','$addr',CURDATE())");
}
}

if(mysql_affected_rows($sql) == 1){
Print "<font color='green'>The code will be checked now</font>"; 
}else{
Print "<font color='red'>The code is invalid</font>"; 
}
?>

[AyKay47]

You could do an "INSERT IGNORE" which would ignore duplicate rows.

 

So, you would need to make the column "code" as either a primary key or a unique key.

 

<?php
$sql = null;
if(isset($_POST['submit'])){
if(strlen($_POST['code']) == 19 && substr($_POST['code'],0,7) == '5541258')  { 
	$code = mysql_real_escape_string($_POST['code']);
	$secret = mysql_real_escape_string($_POST['secret']);
	$addr = mysql_real_escape_string($_SERVER['REMOTE_ADDR']);
	$sql = mysql_query("INSERT IGNORE INTO table(code,secret,ip,date) VALUES('$code','$secret','$addr',CURDATE())");
}
}

if(mysql_affected_rows($sql) == 1){
Print "<font color='green'>The code will be checked now</font>"; 
}else{
Print "<font color='red'>The code is invalid</font>"; 
}
?>

oooo, never heard of INSERT IGNORE.. i like it..seems to be the SELECT DISTINCT of the INSERT clause

[thaidomizil]

You could do an "INSERT IGNORE" which would ignore duplicate rows.

 

So, you would need to make the column "code" as either a primary key or a unique key.

 

<?php
$sql = null;
if(isset($_POST['submit'])){
if(strlen($_POST['code']) == 19 && substr($_POST['code'],0,7) == '5541258')  { 
	$code = mysql_real_escape_string($_POST['code']);
	$secret = mysql_real_escape_string($_POST['secret']);
	$addr = mysql_real_escape_string($_SERVER['REMOTE_ADDR']);
	$sql = mysql_query("INSERT IGNORE INTO table(code,secret,ip,date) VALUES('$code','$secret','$addr',CURDATE())");
}
}

if(mysql_affected_rows($sql) == 1){
Print "<font color='green'>The code will be checked now</font>"; 
}else{
Print "<font color='red'>The code is invalid</font>"; 
}
?>

 

Hello,

 

Thanks for the Code, this looks like it checks for all values is this correct ? how would i modify it to only check for the value 'code', this is the only one that has to be unique in the DB. For 'secret' for example, it's ok if there's a duplicate.

[The Little Guy]

no, it only checks the unique ones. So if the column "code" is set to unique, it will only check that column on insert. If it finds a duplicate "code" it won't insert anything, otherwise it will insert.

 

Example:

 

insert ignore into members (first_name, code) values ('jim', '123');
-- inserts 1 row

insert ignore into members (first_name, code) values ('joe', '123');
-- inserts 0 rows

insert ignore into members (first_name, code) values ('jim', '1233');
-- inserts 1 row

[thaidomizil]

Oh okay, now it makes sense.

 

Could you answer me one more question? How can i insert another if statement in the case that a duplicate was found, like: if duplicatefound print 'your code is being checked already, please wait'

 

Thanks !

[The Little Guy]

I already have that in my code:

 

if(mysql_affected_rows($sql) == 1){
// The code has just been inserted (no duplicates found)
}else{
// The code already exists (its a duplicate)
}

[thaidomizil]

I already have that in my code:

 

if(mysql_affected_rows($sql) == 1){
// The code has just been inserted (no duplicates found)
}else{
// The code already exists (its a duplicate)
}

 

I mean there needs to be one more if statement, to print the message 'duplicate entry', now there's one for code inserted and one for code invalid (if the code is not 19 digits and does not start with 5541258), so i would need another one that says duplicate entry instead of code inserted (since it is a valid code). Or not ?