Remove duplicate entries

[s0bigg]

I am using MySQL to store the RSS feed contents that are updated every few hours. I truncate the database once a day. But during the day, whenever same feeds are updated every few hours I get duplicate entries piled up. Is there any PHP script that I can run to remove those duplicate entries?

[pritaeas]

Why not make that entry a unique index, then you can use a query that updates that index if it cannot be inserted (due to it being already there).

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

[s0bigg]

This is a very good idea, but how can I make it unique when all the columns are variable. Here's what I am loading the table with:

mysql_query("INSERT INTO data values('$title','$link','$description','$date')");

When I re-run my script, all those entries are duplicated in the table. I want to make $title as unique and that will be compared for removal of duplicate entries.

Thanks for your help.

[pritaeas]

Set column `title` as primary key or unique index.

[s0bigg]

OK I changed it to unique index. Now how I do relate it to the example provided in the initial link that you provided? I am little confused.. please help out here!~

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

[pritaeas]

Something like this will insert a new title, or update an existing one with newer additional columns (if that is what you want).

mysql_query("
  INSERT INTO data VALUES ('$title', '$link', '$description', '$date')
  ON DUPLICATE KEY UPDATE link = '$link', description = '$description', date = '$date'
");

[s0bigg]

Cool.. you are awesome!