PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!
Moderator: General Moderators
cap2cap10
Forum Contributor
Posts: 158 Joined: Mon Apr 14, 2008 11:06 pm
Post
by cap2cap10 » Fri Feb 12, 2010 1:46 pm
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
manohoo
Forum Contributor
Posts: 201 Joined: Wed Dec 23, 2009 12:28 pm
Post
by manohoo » Fri Feb 12, 2010 2:08 pm
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.