Finding/Adding Duplicates

[KariParticipant]

Okay, so I have a hopefully somewhat simple question. I know my way around PHP/MySQL a bit, not not as experienced as some.

So, say I have a database filled with the table name “colors” inside it: red, blue, yellow, green, etc. If there were duplicate entries, how would I go about counting them and moving them to a different table?

I’ve googled this and I don’t quite get what comes up. Any help would be appreciated!

[VeraParticipant]

Assuming, I have the initial color table, defined like:

id – int(11)

colors – varchar(20)

I’d do it as follows:

$sql ="SELECT color_name , count( color_name ) AS 'color_items'

FROM colors

GROUP BY color_name";



//retrieve a list of individual colors and the time they appear

$colorCountList = mysql_query($sql);



//loop through the above list

while($row = mysql_fetch_assoc($colorCountList)){



if($row['color_items']>1){ //if color appears more than once



//delete all occurences of it from the table

mysql_query("DELETE FROM colors

WHERE color_name='".$row['color_name']."'");



//insert color_name and the number of appearances into a new table

mysql_query("INSERT INTO my_new_table

SET color_name='".$row['color_name']."',

amount = ".$row['color_items']);

}



}

Is this what you’re looking for?

[amphigoryParticipant]

You could also do it with pure SQL:

INSERT INTO new_table SELECT colors.*

FROM colors INNER JOIN (

SELECT color_name

FROM colors

GROUP BY color_name

HAVING COUNT(*) > 1

) tmp ON colors.color_name = tmp.color_name;



DELETE colors FROM colors INNER JOIN (

SELECT color_name

FROM colors

GROUP BY color_name

HAVING COUNT(*) > 1

) tmp ON colors.color_name = tmp.color_name;

If the tables aren’t identical, however, you’d need to specify columns:

INSERT INTO new_table (color_name, another_column)

SELECT colors.color_name, colors.another_column

[KariParticipant]

Thanks for this guys! This is exactly what I’m looking for I’ll try it out now

[Author]

Posts