Posted By

Abe on 01/29/08


Tagged

mysql database duplicate rows


Versions (?)

Who likes this?

3 people have marked this snippet as a favorite

naz
vali29
webstylr


Remove Duplicate Rows


 / Published in: PHP
 

$duplicates checks and groups duplicated rows with the same field1 and field2 (like comments maybe) and $limit counts how many times a row is duplicated.

  1. <?php
  2. $duplicates = mysql_query("SELECT field1, field2, count(*) FROM table GROUP BY field1, field2 having count(*) > 1");
  3. $count = mysql_num_rows($duplicates);
  4. if ($count > 0) {
  5. while ($row = mysql_fetch_assoc($duplicates)) {
  6. $field = $row["field1"];
  7. $limit = $row["count(*)"] - 1;
  8. mysql_query("DELETE FROM table WHERE field1='$field' LIMIT $limit");
  9. }
  10. mysql_free_result($duplicates);
  11. }
  12. ?>

Report this snippet  

Comments

RSS Icon Subscribe to comments
Posted By: rainier on September 23, 2010

I have a table with item and date created fields. How do I detect duplicate values in the item field compare their date created values get the difference and delete the duplicate item if the difference between the date of the two duplicate values is less than 10 minutes?

Posted By: rainier on September 23, 2010

I have a table with item and date created fields. How do I detect duplicate values in the item field compare their date created values get the difference and delete the duplicate item if the difference between the date of the two duplicate values is less than 10 minutes?

You need to login to post a comment.