Version: 00.999
Type: Full Script
Category: Databases
License: GNU General Public License
Description: Simple loop to find duplicate email addresses. The original problem was to clean the data from a 8000 record database of users. The users had been id’d only by email address and this had not been enforced as unique, so a lot of duplicate records existed. This script loops though and finds teh duplicates.
<?php
###########################################################
/*
This script checks for duplicate records in table.
This is ungodly slow. Takes like 15 minutes to run through
8700 records. Optimize it!
Script does a select, gets the number of total rows. Then
it runs through each one and compares it to all the other
records. If the count of records is more than 1, it imcrem-
ents a counter, echos some stuff and goes on about it's bus-
iness. It could be easily modified to delete the records
instead of just printing the ids.
*/
###########################################################
$db = mysql_pconnect("localhost", "user", "pass");
mysql_select_db(mydb, $db);
$table = "contact";
$query = "select * from $table "; //limit 8700, 100
echo "<font color=blue>Query: $query</font><br>";
$res = mysql_query($query, $db)or die("query failed");
$count = mysql_numrows($res);
echo $count."<hr>";
$i = 0;
while($row = mysql_fetch_array($res)) {
//in my table the 5th column is email, so row[4].
$query2 = "select id from $table where email = '$row[4]'";
//echo $query2."<br>";
$res2 = mysql_query($query2, $db)or die("broken on query2");
//echo mysql_errno().": ".mysql_error()."<BR>";
$num_email = mysql_numrows($res2);
//echo $num_email." $count <br>";
if ($num_email > 1){
echo "id: $row[0], $row[4], count of entries: $num_email<br>n";
$i++;
}
}
echo "Number of duplicates: $i";
?>
<hr>
