how to check mysql for duplicates on insert

[Looktrne]

I have a script that writes emails to a database

 

I need it to check if that email already exists in the database

 

mysql_query("INSERT INTO brain (mail, ism) 
VALUES ('$ie', '1')")or die(mysql_error());  

 

how can I change this code to not inset if the value of mail already exists in the database

 

it also needs to be fast because this script is handling 1000 of inserts

 

thanks

 

Paul

[ag3nt42]

just do a SELECT * FROM [table][tr][td] WHERE column='VALUE'

 

then do an if statement to say wethor or not to do the query

 

if($THIS==$VALUE)

{

//THERE IS A DUPLICATE

}

else

{

//THERE ISN"T A DUPLICATE SO DO INSERT

 

mysql_query("INSERT INTO brain (mail, ism)

VALUES ('$ie', '1')")or die(mysql_error()); 

}

 

something like that

[Looktrne]

I need a faster way

 

will this check 1000's of row's? or one at a time?

 

I need it to check the entire field for duplicate's

 

Paul

[ag3nt42]

that will check the whole table for duplicates

 

and yes it will handle 1000

 

no matter what code you use it will check them each row one at a time..

[ag3nt42]

I need a faster way

 

how do you know you need a faster way if you haven't tried it yet?

[mbeals]

couldn't you just:

 

ALTER TABLE `brain` ADD UNIQUE (`mail`)

 

assuming you have some other primary key.

 

[lemmin]

If you do it that way, all the checking will be done in php and will definately take longer than comparing in the sql.

SELECT * FROM Table WHERE Column = '".$newValue

Where $newValue is a value that you are going to put into the db, but you want to check if it already exists. Then just see if it returned any results and if it did, don't insert it.

if (!mysql_num_rows($query))
  //Insert into

[Looktrne]

couldn't you just:

 

ALTER TABLE `brain` ADD UNIQUE (`mail`)

 

assuming you have some other primary key.

 

 

this sounds like what I am looking for thanks I will test it tomorrow...

[vbnullchar]

if you try to insert a record in a unique field i think it will return some errors better have some checking in php script first

[TransmogriBenno]

better have some checking in php script first

 

For sure, or update the existing record straight away:

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html