[RESOLVED] Delete Duplicates in MySQL using PHP

[transfield]

Hello,
I have a database in MySql which has 2 fields. The 1st field is called id which is the primary key. The 2nd field is called full_add where there are some duplicates.

I would like to delete all the duplicates in the full_add field leaving only 1 record behind.

It does not matter if the corresponding primary key gets deleted as well because the primary key is not related to any other table or database. I prefer not to use temporary tables or create new tables because my webhost does not allocate much memory to me & my database is quite big.

I've written my code half way but I do not know how to continue with the appropriate Query statement. My code is below. Could you please help me to complete it?

Thanks

<?php
$username="abc123";
$password="abc123";
$database="abc123";
$host="localhost";

if(isset($Submit1)){
mysql_connect ("$host","$username","$password") or die(mysql_error());
mysql_select_db($database) or die("Where's the database man?");

//this is where I'm stuck :-(

}
?>   

[qadeer_ahmad]

Hi,
Hope this will your complete solution
Just give TableName where i use in the queries

// Getting all distinct Record from Database
$sql="select distinct full_add as full_add from TableName";
$result=mysql_query($sql);
while($row=mysql_fetch_array($result))
{
	$full_add=$row["full_add"];
	$sql2="select * from TableName where full_add='$full_add'";
	$num=mysql_num_rows(mysql_query($sql2));
	$res=mysql_query($sql2);
	// if $num > 1 then it will contain duplicate records
	if($num > 1)
	{
		$check=0;
		while($row_dup=mysql_fetch_array($res))
		{
			// if $check > 0 that's mean first record is passed
			if($check > 0)
			{
				$id=$row_dup["id"];
				$sql_del="delete from TableName where id=".$id;
				$res_del=mysql_query($sql_del) or die(mysql_error());
			}
			$check++;
		}

}


}

[transfield]

Thank you very much for your neat & well commented code, Qadeer.

Yes, it works very well & I'm able to understand the logic as well.

I appreciate your support on this matter.

God bless you.