Check duplicate before insert into mysql

[thaidomizil]

Hello,

 

i've got this code, to insert some form data into my db, it checks for a value if it exists already, it won't insert, now i want to add another value to check, but i don't know how.

 

This is my code:

 

<?php
if(isset($_POST['submit'])){
   if (strlen($_POST['code']) == 12 && substr($_POST['code'],0,6) == '610147'){
      $code = $_POST['code'];
      $select_query = mysql_query("SELECT * FROM jupiter WHERE code = '$code'");
      if(mysql_num_rows($select_query) == 0){
         $remote_addr = $_SERVER['REMOTE_ADDR'];
         $secret = $_POST['euro'];
         mysql_query("INSERT INTO jupiter(code,euro,ip,date,used) VALUES('$code','$secret','$remote_addr',CURDATE(),'n')");

      }
  Print "<font color='green'>OK</font>"; 
} else { 
    
Print "<font color='red'>Error</font>"; 
}
   
}?>

 

This checks if "code" already exists, now i want to also have it check for "ip"

 

Can someone help me out here please.

 

Thanks !

[The Little Guy]

add 2 sepertate unique indexs one to code, and one to ip

 

then you can do this:

mysql_query("INSERT IGNORE INTO jupiter(code,euro,ip,date,used) VALUES('$code','$secret','$remote_addr',CURDATE(),'n')");
if(mysql_affected_rows() == 0){
// Duplicates found
}else{
// No duplicates were found
}

[thaidomizil]

Hi,

 

thanks for your reply, someone posted the same solution on my original request with checking for "code", i tried setting the Unique Indexes through Phpmyadmin but failed, then someone posted the select query solution, which worked fine, is there a way to extend it to check for both values ?

[The Little Guy]

what do you mean by "extend it to check for both values"?

[thaidomizil]

I mean something like:

 

$select_query = mysql_query("SELECT * FROM jupiter WHERE code = '$code' and ip = '$remote_addr'");

 

I guess that will not work, but just to give you the idea of what i mean.

[The Little Guy]

By using my method, (Which will save your from writing lots of php/mysql, at least more than needed), Go into phpMyAdmin and look a the structure of the table jupiter.

 

Check the boxes  for the fields "code" and "ip". Next go to the bottom of the table and click the button "Unique" this will make it so that BOTH code and ip are unique columns.

 

Code, ip

3werwf, 1.1.1.1

3werwf, 1.1.1.1                        <- This insert will fail

3werwf, 1.1.1.2                        <- This insert will succeed

3w, 1.1.1.1                              <- This insert will succeed

asdfsadfas,12.234.45.544      <- This insert will succeed

asdfsadfas, 1.1.1.1                  <- This insert will succeed

 

Make sense?

[thaidomizil]

I did exactly as you said, checked both boxes, clicked unique underneath, it created a new index, implemented your code, i can't insert the same code twice, but i can enter another code, i shouldn't be able to, as my ip didn't changed.

 

If possible, i want to use the select method and just add IP to it.

[thaidomizil]

I've just re-read your example inserts, this is exactly what i not want, it needs to check for the values individually, if the ip already exists, don't insert, if the code already exists, don't insert,  if  both are Unique, insert

 

12345 - 1.1.1.1 should insert

12345 - 1.1.2.1 should not insert

12344 - 1.1.1.1 should not insert

12111 - 1.3.4.5 should insert

[jcbones]

I've just re-read your example inserts, this is exactly what i not want, it needs to check for the values individually, if the ip already exists, don't insert, if the code already exists, don't insert,  if  both are Unique, insert

 

12345 - 1.1.1.1 should insert

12345 - 1.1.2.1 should not insert

12344 - 1.1.1.1 should not insert

12111 - 1.3.4.5 should insert

 

If you make code and IP both unique keys, mysql will throw an error on insert if EITHER code OR ip exist in the table.

[Psycho]

The proposed solution "The Little Guy" provided with respect to the unique field conditions would make the field combinations unique (as displayed in his example). If you want to prevent a duplicate in either column separately, then make then unique individually. In phpmyadmin simply go to the structure tab for the table and click the unique icon on the right side for each column.