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.

Expand | Embed | Plain Text

<?php
$duplicates = mysql_query("SELECT field1, field2, count(*) FROM table GROUP BY field1, field2 having count(*) > 1");
$count = mysql_num_rows($duplicates);
if ($count > 0) {
	while ($row = mysql_fetch_assoc($duplicates)) {
		$field = $row["field1"];
		$limit = $row["count(*)"] - 1;
		mysql_query("DELETE FROM table WHERE field1='$field' LIMIT $limit");
	}
	mysql_free_result($duplicates);
}
?>

Report this snippet Tweet

Comments

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?

Comment:

You need to login to post a comment.