[SOLVED] how to check duplicate entries in mysql

[pixeltrace]

Hi,

 

I imported a csv file in mysql.

i am using phpmyadmin to import csv files in mysql database.

 

how do can i check or what query will i use to check if there

are any duplicate items in the table?

 

hope you could help me with this.

 

thanks!

[ToonMariner]

This is fairly straight forward....

 

if the table has a primary key then each record will be different.

 

what you need to do is check each entry againts every other enrty where the primary key is different but everything else is the same...

 

so for this example I have a table 'test' with 3 fields 'key','text' and 'stuff'...

 

$qry = "SELECT `test`.* FROM `test` AS `t1`, `test` AS `t2`WHERE `t1`.`key` != `t2`.`key` AND `t1`.`text` = `t2`.`text` AND `t1`.`stuff` = `t2`.`stuff`";
$qry = mysql_query($qry);
while($row = mysql_fetch_assoc($qry)
{
print_r($row);
echo "<br />";
}

 

This should print out all duplicates - make sure you only delete all but ONE of each entry - they will come in sets of 2 or more remember!!!

[pixeltrace]

thanks!

 

because i need to check if there are any username and email duplicates in my table.

hope you could help me what php script or sql query to use to check this.

 

thanks again!

[pixeltrace]

hi!

 

any help on this please.

 

thanks!

[pixeltrace]

hi,

 

i tried this codes, im not sure if this is correct.

<?php 

$dbhost = 'localhost';
$dbusername = 'username';
$dbpasswd = 'password';
$database_name = 'db';

	$connection = mysql_pconnect("$dbhost","$dbusername","$dbpasswd") 
	or die ("Couldn't connect to server.");

$db = mysql_select_db("$database_name", $connection)
	or die("Couldn't select database.");


$qry = "SELECT `jos_muse_users`.* FROM `jos_muse_users` AS `t1`, `jos_muse_users` AS `t2`WHERE `t1`.`username` != `t2`.`email`";
$qry = mysql_query($qry);
while($row = mysql_fetch_assoc($qry)
{
print_r($row);
echo "<br />";
}
?>

 

currently i am getting a parsing unexpected '}' in line 18.

 

hope you could help me solve this.

i wanted to know if i have duplicated entries for the username, and email.

 

thanks so much!

[MadTechie]

change

<?php while($row = mysql_fetch_assoc($qry)?>

 

to

 

<?php
while($row = mysql_fetch_assoc($qry))?>

[MadTechie]

also

 

The SQL statement should be

 

<?php
$qry = "SELECT jos_muse_users.* FROM jos_muse_users AS t1, jos_muse_users AS t2 WHERE t1.username != t2.username AND t1.email != t2.email";
?>

 

EDIT:

Thats for users who have the same username and email in the table more than once..

it will not find duplicate emails if their username is different

 

 

WIll find

user1 user1@emails.com

user1 user1@emails.com

 

will NOT find

user1 user1@emails.com

user2 user1@emails.com

 

will NOT find

user1 user1@emails.com

user1 user2@emails.com

[pixeltrace]

Hi,

 

i am still getting an error

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /var/www/html/mumcentre/qry.php on line 17

 

this is my current code for the page

 

<?php 

$dbhost = 'localhost';
$dbusername = 'username';
$dbpasswd = 'password';
$database_name = 'db';

	$connection = mysql_pconnect("$dbhost","$dbusername","$dbpasswd") 
	or die ("Couldn't connect to server.");

$db = mysql_select_db("$database_name", $connection)
	or die("Couldn't select database.");


$qry = "SELECT jos_muse_users.* FROM jos_muse_users AS t1, jos_muse_users AS t2 WHERE t1.username != t2.username AND t1.email != t2.email";
$qry = mysql_query($qry);
while ($row = mysql_fetch_assoc($qry))
{
print_r($row);
echo "<br />";
}
?>

 

hope you could help me with this.

also, let say there are duplicate entries, how can i delete the

duplicates? what is the code for this?

i found a Mysql Delete Duplicate entries software but its not working.

 

need help on this. thanks!

[pixeltrace]

Hi my problem is solved already and i found a solution which i wanted to share also

------------------

STEP 1

-	Create new table to have zero duplicates
mySQL Code 
--------------
CREATE TABLE new_table AS
SELECT *
FROM old_table
GROUP BY email
HAVING ( COUNT(email) = 1 )

STEP 2

-	Rename old_table to another name and rename new_table to old_table
mySQL Code 
--------------
(for backup purposes)
RENAME TABLE old_table TO old_table_old; 

RENAME TABLE new_table TO old_table;

 

Thanks again!