How can GROUP BY and COUNT be utilized in PHP to identify and delete duplicate entries in a database table?

When dealing with duplicate entries in a database table, we can use the GROUP BY and COUNT functions in SQL to identify the duplicates based on specific columns. We can then use PHP to loop through the results, identify the duplicate entries, and delete them from the table. 

<?php
// Connect to the database
$pdo = new PDO("mysql:host=localhost;dbname=database", "username", "password");

// Query to identify duplicate entries based on specific columns
$query = "SELECT column1, column2, COUNT(*) as count FROM table_name GROUP BY column1, column2 HAVING count > 1";

// Execute the query
$stmt = $pdo->query($query);

// Loop through the results and delete duplicate entries
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $delete_query = "DELETE FROM table_name WHERE column1 = '{$row['column1']}' AND column2 = '{$row['column2']}' LIMIT {$row['count'] - 1}";
    $pdo->query($delete_query);
}

echo "Duplicate entries have been successfully deleted.";
?>

Keywords

GROUP BY COUNT PHP database table duplicate entries

Related Questions