[seans9]

ok im not that advanced in php...but here is the deal. i have a database with a lot of email addresses (some of which are duplicates). each email address has its own row. what i want to do is go through and delete the rows with duplicate ones (leaving at least one of the email addresses)...i hope that makes sense. this is what i wrote and its not working. PLEASE help. thanks

PHP Code:

$query="SELECT * FROM `emails` ORDER BY `email` ASC LIMIT 0, 30";

mysql_query($query);

$result=mysql_query($query); 



$numrows=mysql_num_rows($result);



$totalemailsdeleted=0;

$i=1;

$i2=0;

while($i2 < $numrows)

{

    $id=mysql_result($result, $i2, id);

    $email=mysql_result($result, $i2, email);

    while($i < $numrows)

    {

        $idcompare=mysql_result($result, $i, id);

        $emailcompare=mysql_result($result, $i, email);

        if($emailcompare == $email)

        {

            $query="DELETE FROM `emails` WHERE `id` = '$idcompare' LIMIT 1";

            mysql_query($query);

            $emailsdeleted.="$emailcompare<br>";

            $totalemailsdeleted++;

            $numrows=mysql_num_rows($result);

        }

        else

        {

        }

        $i++;

    }

    $i2++;

} 

[blaw]

Hi there,

I'm not sure if you've figured out yourself, but for future reference...

ASSUMING that your `emails` table has only one field `email` (besides your PK that you are willing to let go), you can apply DISTINCT keyword and put the results into a new table.

Code:

SELECT DISTINCT email FROM emails;

Run this SQL statement, and you will get unique email addresses. Say, if you have:

Code:

+----+----------------+
| id | email          |
+----+----------------+
|  1 | amy@foo.com    |
|  2 | bob@bar.net    |
|  3 | amy@foo.com    |
|  4 | cathy@hoge.org |
|  5 | bob@bar.net    |
+----+----------------+

Then the above SQL statement with DISTINCT keyword will give you this:

Code:

+----------------+
| email          |
+----------------+
| amy@foo.com    |
| bob@bar.net    |
| cathy@hoge.org |
+----------------+

Then, you can use a loop or something to insert these emails into a new table or generate a CSV file and import it from MySQL.

HTH.

[offsound]

You could use phpmyadmin, mysql console or even php..

Backup your table first because i didn't test this, but try these queries:

Code:

CREATE TABLE emails_temp AS SELECT * FROM emails WHERE 1 GROUP BY email;

DROP TABLE emails;

RENAME TABLE emails_temp TO emails;

[amigura]

Code:

CREATE TABLE emails_temp AS SELECT * FROM emails WHERE 1 GROUP BY email;

DROP TABLE emails;

RENAME TABLE emails_temp TO emails;

be careful in doing this as it will kill all your primary,index, auto increm


this is a rework of wat you were trying to do

Code:

$sql = "SELECT id,count(email) as etot FROM emails group by email having etot > 1"; 
$result = mysql_query( $sql );while( $row = mysql_fetch_array( $result ) ) {

$id.= $row["id"].',';
//$delem = "delete from emails where id = '".$row["id"]."'"; $delquery = mysql_query($delem);
}

$id=substr($id, 0, -1);
$delem = "delete from emails where id in ($id)"; $delquery = mysql_query($delem);

[iseecom]

Hi Guys!

Here is my solution ... and I have tested it ... it works ..

PHP Code:

function RemoveDuplicates($TableName, $UniqueFieldName, $IDFieldName, $FirstFoundIDValue)
{
            $Query =    "DELETE  FROM ".$TableName.
            
                            " WHERE    ".$IDFieldName." IN ".
                            
                                "( SELECT    a.".$IDFieldName." FROM ".$TableName." a, ".$TableName." b 
                                
                                        WHERE    (a.".$UniqueFieldName." = b.".$UniqueFieldName.") AND (a.".$IDFieldName." > ".$FirstFoundIDValue.")

                                        GROUP BY a.".$IDFieldName."

                                        HAVING COUNT(a.".$UniqueFieldName.") > 1
                                 )";
// Example Use:            
//delete  from tblps_CommEquip where PSID in    ( select a.PSID from tblps_CommEquip a, tblps_CommEquip b     where    (a.CommEquipType = b.CommEquipType) AND (a.PSID > 80)    
//                      group by a.PSID having count(a.CommEquipType) > 1)
            
            db_query($Query);

}