Update duplicate MySQL record using PHP

I need to update the duplicate records in database using PHP.

For example:

I have three identical record micheal in the database. I need to u开发者_Go百科pdate the two micheal records into micheal 1 and micheal 2, but left one micheal as is.

---

Try....

UPDATE yourdata
SET name='michael 1'
WHERE name='michael'
LIMIT 0,1;

UPDATE yourdata
SET name='michael 2'
WHERE name='michael'
LIMIT 0,1;

Then add a unique index to prevent it happening again.

---

$query = "SELECT * FROM table ORDER BY name";

$result = mysql_query($query, $db) or die('MySQL Error: ' . mysql_error()); // if mysql_query fails, prints the error reported by the mysql server and stops execution

$lastName = "";
$i = 1;
while($row = mysql_fetch_assoc($result)) {

    if ( $lastName==$row['name'] ){
        $q = "UPDATE table set name ='".$row['name']." ".$i."' WHERE id = ".$row['id'];
        mysql_query($q, $dbh) or die ("problema con query");
        $i++;
    }

    else
        $i = 1;

    $lastName = $row['name'];
}

If you have problems with concurrent modifications, try to store the ids and the names as an array and launch the updates.