Delete duplicate entries. Help please :)

Hello im writing a script for one of my databases. and im stuck. The problem with it is that it has duplicate entries like:

This is One Entries's Name

This is one entrie's name

I want to keep only one of them... here is the code i got so far... im stuck in this part looky:

$lyrics_dup=array(); 

$resultID = mysql_query("SELECT * FROM `lyrics` WHERE `song`!=''", $dbh); 
		while ($roww = mysql_fetch_array($resultID)) {  
	  	  $id="$roww[id]";
		  $song="$roww[song]";

$resultID2 = mysql_query("SELECT * FROM `lyrics` WHERE `song` LIKE '%$song%'", $dbh); 
		while ($roww = mysql_fetch_array($resultID2)) {  
	  	  $id2="$roww[id]";
		  $song2="$roww[song]";
			{
/*				$lyrics=array();
array_push($lyrics, $id2);
$lyricscount = count($lyrics);

if ($lyricscount>="2"){
array_push($lyrics, $lyrics_dup);
unset($lyrics);
} else {
}
*/

array_push($lyrics_dup,$id2);
print "$id2 - $song2<br>";
			}

	  }
		}

$lyrics_dup_count=count($lyrics_dup);

print "$lyrics_dup_count";

PHP:

Im having the problem where you see the /* */ commented code... i cant figure out how to add the duplicate entries to the array only if the second query returns more than 2 entries.

i'm not that good at php... i know this is probably very simple, any help greatly appreciated.

Thanks!

 

You could do it by changing the SQL to return a result set just returning records with more than one entry, something like (untested):

$resultID = mysql_query("SELECT l1.* FROM `lyrics` l1
inner join l2 on l1.song = l2.song and l1.id <> l2.id
WHERE l1.song!=''
group by l1.song", $dbh); 

Code (php):

You can now just loop through the result set doing the deletes as you go.

This is very much untested but will hopefully help you out a bit

 

But with this i can't delete just one of them can i? That code is rather confusing for me ive never seen something like that before :/

What i was thinking was set each repeated group in an array and have it add one of the tow array entries to the delete array. =\


can you just explain to me what those l1s and l2 mean? I tried the query throug php my admin and it gave me an error that - Table 'turnitupfor.l2' doesn't exist

 

Sorry query should have been

$resultID = mysql_query("SELECT l1.* FROM lyrics l1
inner join lyrics l2 on l1.song = l2.song and l1.id <> l2.id
WHERE l1.song <> ''
group by l1.song", $dbh); 

Code (php):

What the SQL is doing is a self join, i.e. joining the lyrics table to itself where the title is the same but the id is different. So the result set should be a set of songs that are occur more than once.

 

i run it as:

SELECT l1.* FROM `lyrics` l1INNER JOIN `lyrics` l2 ON l1.song = l2.song and l1.id <> l2.id group WHERE l1.song <> ''  by l1.song

PHP:

in php my admin and it tells me i have a syntax error, any ideas? i put in the `` but they didnt help.

 

It's difficult doing this without testing (and at 12.30 after getting back from the boozer) but try

SELECT l1.* FROM `lyrics` l1 INNER JOIN `lyrics` l2 ON l1.song = l2.song and l1.id <> l2.id WHERE l1.song <> '' group by l1.song 

Code (sql):

All I've done is add a space and moved a syntax error

 

Thank you ,that worked ill check out the results n figure what to do thanks agian

 

No probs, happy to help rather than hanging out in general chat for a change

 

Thank you very much once again it worked perfectly and i learned some new SQL commands I really appreciate it! Left you some green!