Php / Mysql help needed...
 

Anyone have code or know how to delete duplicate rows in a larger database. I have about 55k rows and want to remove all the duplicate urls in it.

Thanks

/
dev nul

delete
 

backup first

if you know what your deleting

"hair in my cabbage" is all the data in that field for example

delete from <table> where <tableelement> LIKE 'hair in my cabbage'

backup first!!!!!!!!! dont blame me later

I'd suggest that the easiest way to do it is through a temporary table... do a SELECT DISTINCT into the temp table to eliminate the duplicates.

Alternately, a cute way to do a de-facto culling is to ALTER TABLE and make your url column UNIQUE, ignoring (and therefore dropping) duplicates:

ALTER IGNORE TABLE sometable ADD UNIQUE (url)

... which would serve the additional purpose of permanently removing the possibility of duplicate URLs in the future, as well.

Obviously you'll want to back up everything before you try any of this. :thumbsup

I'd do something like this (but backup first)

$rs = mysql_query("SELECT id, url FROM your_table ORDER BY id ASC");
while (list($id,$url)=mysql_fetch_row($rs)) {
mysql_query("DELETE FROM your_table WHERE url = '$url' AND id != '$id'");
}

that will go through all your rows and delete all duplicates other than the first one ... you could change ASC to DESC to make it delete all but the newest ... obviously you have to probably change the field and table names though.

Awesome, that alter thing was the easiest way!

thank you!

:thumbsup