Delete Duplicate Rows

[jj20051]

Hello,

 

I Have a bunch of duplicate rows in my database. I Realize this wouldn't have happened had i actually made a certification of it. Now i just need to remove all of the duplicates. Can someone please help?

[dropfaith]

how many rows are we talking about here?

[jj20051]

More Than 10... I'd say Around 30 Duplicates.

[jj20051]

I Still Need Some Help. Someone Please?

[premiso]

One way to do it:

 

<?php
// sql connection above

$sql = "SELECT * FROM tbl_name ORDER BY name";
$res = mysql_query($sql);
while ($row = mysql_fetch_assoc($res)) {
    if ($row['name'] == $oldName)
        continue;

    $delSQL = "DELETE FROM tbl_name WHERE id = " . $row['id'];
    mysql_query($delSQL);

    $newSQL = "INSERT INTO tbl_name (col1, col2, col3) VALUES ('" . $row['id'] . "', '" . $row['name'] . "', '" . $row['happy'] . "')";
    mysql_query($newSQL);
    echo $newSQL;
    $oldName = $row['name'];
}
?>

 

One word of warning, I would do a run through it with just the insert part and make sure the query is getting formatted correctly. I would also back up the current table data, just incase everything gets deleted and nothing gets re-populated.

 

Replace $row['name'] with an identifier of how you can if it is a duplicate.

 

But as a stated above, backing up your data is a must as this could delete all your rows etc and then your SOL.