how to remove duplicate entries from mysql database

[darkc99]

<?php
// Duplicate entries remover from database
// task :: load url check if more than 2 entries exists replace it with new entry
session_start();
// no time limit
set_time_limit(0);  
// connect to db 
include ("config.php"); 
// select data from database   target woth and domain table
$result = mysql_query( "SELECT * FROM domain" )     // table domain    wwww.domain.com
or die("SELECT Error: ".mysql_error());
$resultx = mysql_query( "SELECT * FROM worth" )    // table worth   domain.com
or die("SELECT Error: ".mysql_error());
$num_rows = mysql_query($result);               // Loading table domain
$num_rowsx = mysql_query($resultx);				// Loading table worth
//duplicate url counter
$dcounter=0;
// fetching data 
while ($get_infox = mysql_fetch_assoc($resultx) && $get_info = mysql_fetch_assoc($result))
{

	// Loading Urls for Comparing
	    $wurl=$get_infox[domain];         // Loading url from table worth
		$idx=$get_infox[id];    // Loading Id of table worth
		$durl=$get_info[domain];	 	// Loading url from table domain		
		
		if ($durl != $wurl[domain])			// Comapring Table 'Domain' Url With All Urls of Table 'worth'
		{
			// Load id too 
			// use id to separate domains
			// let id =   $id;
			if ($wurl == $wurl[domain] && $idx != $idx[id])			// Comapring one Url With All Urls of Table 'worth'
				{	
				//$dcounter++;  // ++ dcounter mean matched found its should be 2 or greater becusae 1 is itself
					//if($dcounter == 2)
						//{
						mysql_query("INSERT INTO worth (domain) VALUES ('".$durl."') WHERE domain='".$wurl."'");	  // insert 
						echo "url $durl replacement success Replaced by".$durl.".";
						mysql_query("DELETE FROM domain WHERE domain='".$durl."'");
						//}
							if($dcounter == 1)
								{
								echo "url checked and ok ";							// no multiple match so ok
								}
						}
				}
}
mysql_close($con);                // close connection to database

?>

i have large database so i cant remove duplicate remove manually
so i write little code for it but not working properly
my task ::
i have one table worth there i have two columns id and domain now i have to find duplicate entries in it by checking for domain column if 2 same domain column values then duplicate entry , after that i have to replace one of these two entries with new value to domain column .
so little confuse please help

[ChrisPadgham]

seems awefully hard work. Why don't you just create a new table with the key field as unique and append the existing table, the new table will not have any duplicates

[darkc99]

seems awefully hard work. Why don't you just create a new table with the key field as unique and append the existing table, the new table will not have any duplicates

i know your way is also good and can solve the problem but my database is large and there too many columns in it and its hard too copy i just mentioned id and domain column because duplicate values are in domain table only but can be handled somehow by logic with id and domain column
so am thinking of logic that within database i compare one by one entry with entire database entries and match their domain column if matched then replace it with new value .

id domain
1 facebook.com
2 daniweb.com
3 daniweb.com
4 google.com

like that somehow it above 30,000 id and other random location i have duplicate entries which are hard to find manually and rmeove

[darkc99]

finally after working for 30 min on my problem i made solution for it :) too lazy am i thought i will get solution here and someone solve the problem for me :D but we should do our own works by self

<?php
// Duplicate entries remover from mysql database
// task :: load url check if more  2 duplicate entries exists replace it with new entry
session_start();
// no time limit
set_time_limit(0);  
// connect to db 
include ("config.php"); 
// select data from database   target woth and domain table
$result = mysql_query( "SELECT * FROM domain" )     // table domain    wwww.domain.com
or die("SELECT Error: ".mysql_error());
$resultx = mysql_query( "SELECT * FROM worth" )    // table worth   domain.com
or die("SELECT Error: ".mysql_error());
// fetching data 
while($row=mysql_fetch_array($result) && $rowx=mysql_fetch_array($resultx))
{
	// Loading Url and id from worth
	    $wurl=$rowx["domain"];         // Loading url from table worth
		$wid=$rowx["id"];    // Loading Id from table worth
		// for replacement load url on table domain
		$durl=$row["domain"];	 	// Loading url from table domain		
		if ($durl != $wurl[domain] && $durl != "")			// Comapring Table 'Domain' Url With All Urls of Table 'worth'
		{
			// Load id too 
			// use id to separate domains
			// let id =   $id;
			if ($wurl == $wurl[domain] && $wid != $wurl[id])			// Comapring one Url With All Urls of Table 'worth'
				{	
						mysql_query("INSERT INTO worth (domain) VALUES ('".$durl."') WHERE domain='".$wurl."'");	  // insert 
						echo "url:: $wurl replacement success, Replaced by $durl ";
						// Delete it the inserted url
						mysql_query("DELETE FROM domain WHERE domain='".$durl."'");
						}
						
				}
}
mysql_close($con);                // close connection to database

?>