Page 1 of 1

Trying to remove duplicate fields from Mysql database!!!!

Posted: Fri Feb 12, 2010 1:46 pm
by cap2cap10
Hello, again php technorati. I bring another one of my issues to your doorstep.
I am trying create a php script that removes duplicate Fields/Rows from MySQL db.
Here is the code:

Code: Select all

$qry = "select email from mail_list_2";
$db=query( $qry );
while( $data = $db->fetchArray() ) {
 
$qry1 = "select * from mail_list_2 where email='".$data['email']."' ";
$db1=query( $qry1 );
if($db1->numRows() > 1)
 
    {
$check=0;
while( $data1 = $db1->fetchArray() ) {
 
 
if($check > 0)
            {
 
 
####### add delete query here
 
$sql_del="delete from mail_list_2 where email=".$data['email'];
                $db->query( $sql_del );
 
 }
            $check++;
 
}
 
}
 
Here is the error:
Fatal error: Call to undefined function query() in *********************** on line 315
Please enlighten.

Thanks in advance

Batoe

Re: Trying to remove duplicate fields from Mysql database!!!!

Posted: Fri Feb 12, 2010 2:08 pm
by manohoo
You can't remove duplicate fields, since you can't have duplicate field names in the same table.

However, you can remove duplicate records (a.k.a rows). From your post I understand that you want to remove duplicate emails, correct?

This is a 3 step process:

1. Move the non duplicates to a temporary table
CREATE TABLE mail_list_temp AS
SELECT * FROM mail_list_2
GROUP BY email;

2: delete old table
DROP TABLE mail_list_2;

3: rename the temporary table
RENAME TABLE mail_list_temp TO mail_list_2;

Done!

But.... if you don't want to have duplicate email records in the future I suggest that you modify your table:

4: prevent duplicate emails:
ALTER IGNORE TABLE mail_list_2 ADD UNIQUE KEY(email)

You can do all of the above in MYSQL, it would be much more efficient than doing it in PHP.