Prevent Duplicate Entrie

[CosminStan]

Heloo guys i have a problem wit a script

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root';
$dbname = 'site';
 $con = mysql_connect($dbhost, $dbuser, $dbpass);
   if (!$con)
   {
   die(mysql_error());
   }
 mysql_select_db($dbname, $con);
  $sql = "REPLACE INTO stiri (id, titlu, continut, link, categorie, data) VALUES ('','$titlu','$text','$link','Liga 1','$data')"; 
  mysql_query($sql);
 mysql_close($con);

This part is in a php foreach part and every time i run the script i get duplicate entrie, how can i solve this. Thx!

[Barand]

Have you looked at the data in the table to see if there are duplicates?

 

How are you outputting the data when you see duplicates?

[CosminStan]

i saw them in phpmyadmin..

[Barand]

Do you have a primary key or other unique key?

Edited November 14, 2012 by Barand

[CosminStan]

the id is primary, but unique i want to put link (url) but it's big like 126-135 chars...

[Barand]

REPLACE can only check for dupes if it knows something must be unique.

 

You'll have to set an index or check yourself first.

[CosminStan]

I've tried insert and insert ignore with the same result

[Barand]

no surprises there then.

 

You could wait for MySQL v6 - believe it will have a built-in clairvoyance feature so it will detect duplicates without you telling it which column should be unique.

Edited November 14, 2012 by Barand

[CosminStan]

This is your answer to my problem Thank you very much, you could let the other topic unlocked an lock this one cause this is not a solution to way for something.

[Barand]

It does not matter whether you use REPLACE or INSERT...IGNORE, the only way that MySQL knows an item is an unwanted duplicated is to set those columns that cannot be duplicated as UNIQUE.

 

If you don't want to to do that then it up to you to validate that items do not already exist. Am I getting through?

 

BTW - if you are storing dates in "j M Y" format then it is as much use as a chocolate teapot.

Edited November 14, 2012 by Barand

[PFMaBiSmAd]

Also, you should NOT be repeatedly creating a database connection and then closing it inside of a loop. Your application should create a database connection once and let php close it when the code on the page ends.

[CosminStan]

Thanks PFMaBiSmAd, but how can i do that ?

@Barand is it ok to use as UNIQUE a varchar(250) ? how would thw server react to this ?

Thanks.

[Barand]

Index lengths can be 1000 chars for MyISAM and 767 for InnoDB tables so no prob with varchar(255)