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.
精彩评论