Duplicate Entries

[scott532]

Hello, I apologize in advance for being new to working with MySQL and databases in general...

 

I'm running MySQL 4.1.11

 

Table structure is:

salons(id, name, email, salon_name, address, city, state, zip, phone, website)

 

I hope this is sufficient info for help

 

I am trying to take out duplicate rows from my table.I now realize I should have used a unique index on all my user emails. What is the best way to deal with this situation? Should I create a new table with the unique rows, or should I delete all duplicate entries?

 

I found the duplicates with this:

SELECT email, count(*) AS duplicates

  FROM salons

GROUP BY email

HAVING duplicates > 1

ORDER BY duplicates;

 

Where do I go from here?

 

Thanks,

Scott

 

[Barand]

Backup table first (just in case - untested code!)

 

<?php
$sql  = "SELECT email, count(*) AS duplicates
            FROM salons
            GROUP BY email
            HAVING duplicates > 1
            ORDER BY duplicates";
$res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");
while (list($email, $count) = mysql_fetch_row($res))
{
    $lim = $count - 1;
    $sql2 = "DELETE FROM salons 
                WHERE email = '$email'
                ORDER BY id DESC
                LIMIT $lim";
    mysql_query($sql2);
}
?>

should leave the original records intact, at least that's the theory)