Exoon Posted December 1, 2007 Share Posted December 1, 2007 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 Link to comment https://forums.phpfreaks.com/topic/79751-getting-rid-of-duplicates/ Share on other sites More sharing options...
Barand Posted December 2, 2007 Share Posted December 2, 2007 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 Link to comment https://forums.phpfreaks.com/topic/79751-getting-rid-of-duplicates/#findComment-403895 Share on other sites More sharing options...
Exoon Posted December 2, 2007 Author Share Posted December 2, 2007 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. Link to comment https://forums.phpfreaks.com/topic/79751-getting-rid-of-duplicates/#findComment-403898 Share on other sites More sharing options...
PHP_PhREEEk Posted December 2, 2007 Share Posted December 2, 2007 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 Link to comment https://forums.phpfreaks.com/topic/79751-getting-rid-of-duplicates/#findComment-403930 Share on other sites More sharing options...
cooldude832 Posted December 2, 2007 Share Posted December 2, 2007 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."; } ?> Link to comment https://forums.phpfreaks.com/topic/79751-getting-rid-of-duplicates/#findComment-403932 Share on other sites More sharing options...
Barand Posted December 2, 2007 Share Posted December 2, 2007 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 Link to comment https://forums.phpfreaks.com/topic/79751-getting-rid-of-duplicates/#findComment-404160 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.