Getting rid of duplicates

[Exoon]

Hello. ive got a database full of games and rather then go through and delete 1 by 1 is there a way i can just delete all the duplicates

 

Ive looked on google and i noticed you can select Distinct but the problem with that is.

 

I have some games that are on more then 1 platform and i want to keep them games in the database for example

 

1. Need for speed || xbox 360

2. Need for speed || ps3

3. Need for speed || wii

4. Need for speed || ps3

5. Need for speed || xbox 360

 

I want to keep number 1,2 and 3 but remove 4 and 5 because the Name and Console are duplicates.

 

Hope this makes sense and someone can help.

 

Thanks

Exoon

[Barand]

One way is to create a new table and write one of each combination of name/console to it. Then delete the originals and replace with the unique ones.

 

You have a much bigger problem if the primary keys of these records are foreign keys in other tables

[Exoon]

Nah, i dont have any foreign keys linking to this table at the moment. I was building up this table before i started on a new one.

 

How exactly do you mean write one of each combination because if i make a DISTINCT query i can only grab information from one i cant do NAME & CONSOLE because it returns nothing.

 

How do you suggest i go about it.

[PHP_PhREEEk]

Build arrays, toss dupes with array_unique.

 

<?php
$testArray['Need for speed'] = array('xbox 360', 'ps3', 'wii', 'ps3', 'xbox 360');
echo "<pre>";
print_r($testArray);
$testArray = array_unique($testArray['Need for speed']);
print_r($testArray);
echo "</pre>";

/*
Prints:

Array
(
    [Need for speed] => Array
        (
            [0] => xbox 360
            [1] => ps3
            [2] => wii
            [3] => ps3
            [4] => xbox 360
        )

)
Array
(
    [0] => xbox 360
    [1] => ps3
    [2] => wii
)
*/

 

Putting that logic into a loop will allow all records to be purged of duplicate platforms for each title:

 

<?php
$sql = "SELECT `title` FROM `games`";
if ( !$result = mysql_query($sql) ) {
    die('MySQL Error!<br>SQL:<br>' . $sql . '<br><br>Error: ' . mysql_error());
}
if ( mysql_num_rows($result) > 0 ) {
    // fill array with titles
    while ( list($title) = mysql_fetch_assoc($result) ) {
        $titles[] = $title;
    }
    // remove dupes, leaving us with unique titles
    $titles = array_unique($titles);
} else {
    echo 'No Titles were found in the table! Check table name in SQL:<br>' . $sql;
}
foreach ( $titles as $val ) {
    unset($platforms);
    $sql = "SELECT `platform` FROM `games`
            WHERE `title` = '$val'
           ";
    if ( !$result = mysql_query($sql) ) {
        die('MySQL Error!<br>SQL:<br>' . $sql . '<br><br>Error: ' . mysql_error());
    }
    // we know there's at least one row for each title, no need for num_rows check
    while ( list($platform) = mysql_fetch_assoc($result) ) {
        $platforms[] = $platform;
    }
    // purge dupes
    $platforms = array_unique($platforms);
    // delete all records associated with title
    $sql = "DELETE FROM `games`
            WHERE `title` = '$val'
           ";
    if ( !$result = mysql_query($sql) ) {
        die('MySQL Error!<br>SQL:<br>' . $sql . '<br><br>Error: ' . mysql_error());
    }
    // rebuild all records using unique array
    foreach ( $platforms as $val2 ) {
        $sql = "INSERT INTO `games`
               (`title`, `platform`)
                VALUES
               ('$val', '$val2')
               ";
        if ( !$result = mysql_query($sql) ) {
            die('MySQL Error!<br>SQL:<br>' . $sql . '<br><br>Error: ' . mysql_error());
        }
        // Insert another record, or process another title

    }
    // Process another title, or exit script
}
die('Script completed successfully!');
?>

 

PhREEEk

[cooldude832]

try

<?php
//Connect to SQL
$q = "Select PrimaryKey, GameName, Platform from `Table`";
$r = mysql_query($q) or die(mysql_error();
if(mysql_num_rows($r) >0){
$games = array();
while($row = mysql_fetch_array($r)){
	if(!in_array($row['GameName']."_".$row['Platform'],$games)){
		$games[] = $row['Game_Name']."_".$row['Platform'];
	}
	else{
		$delete[] = $row['PrimaryKey'];
	}
}
if(!empty($delete)){
	$q2 = "Delete from `Table` Where ";
	foreach ($delete as $value){
		$q2 .= "PrimaryKey = '".$value."' ||";
	}
	$q2 = substr($q2,0,strlen($q2)-2);
	$r2 = mysql_query($q2) or die(mysql_error())."<br />".$q2);
	echo "Delted ".count($delete)." Records";
}
else{
	echo "No Duplicated Records Found";
}
}
else{
echo "No records Found.";
}
?>

 

[Barand]

Nah, i dont have any foreign keys linking to this table at the moment. I was building up this table before i started on a new one.

 

How exactly do you mean write one of each combination because if i make a DISTINCT query i can only grab information from one i cant do NAME & CONSOLE because it returns nothing.

 

How do you suggest i go about it.

 

CREATE TABLE copygames SELECT DISTINCT name, console FROM games