Some example code to delete duplicate users from the db

db_duplicates.php

<?php

$mysqli = new mysqli("db", "user", "password", "test_db");

// Check connection
if ($mysqli->connect_errno) {
  echo "Failed to connect to MySQL: " . $mysqli->connect_error;
  exit();
}

//create a table named users with an auto incrementing id and a name field of 255 characters and an email field
//if the table already exists, this will return false and not do anything
//if you want to drop the table first, use $mysqli->query("DROP TABLE IF EXISTS users");
if ($mysqli->query("CREATE TABLE IF NOT EXISTS users (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(255), email VARCHAR(255), PRIMARY KEY (id))") === TRUE) {
  echo "Table users created successfully";
} else {
  echo "Error creating table: " . $mysqli->error;
}

// //insert 1000 rows into the users table with random names and emails and randomly duplicate some of the email columns
// for ($i = 0; $i < 1000; $i++) {
//   $name = substr(str_shuffle("abcdefghijklmnopqrstuvwxyz"), 0, 10);
//   $email = substr(str_shuffle("abcdefghijklmnopqrstuvwxyz"), 0, 10) . "@" . substr(str_shuffle("abcdefghijklmnopqrstuvwxyz"), 0, 5) . ".com";
//   if (rand(0, 1) == 1) {
//     $email = substr(str_shuffle("abcdefghijklmnopqrstuvwxyz"), 0, 10) . "@" . substr(str_shuffle("abcdefghijklmnopqrstuvwxyz"), 0, 5) . ".com";
//   }
//   $mysqli->query("INSERT INTO users (name, email) VALUES ('$name', '$email')");
// }

//tell me how many rows in the database have duplicate email fields and print the resulting rows as a table
$result = $mysqli->query("SELECT email, COUNT(*) AS count FROM users GROUP BY email HAVING count > 1");
echo "<table border='1'><tr><th>Email</th><th>Count</th></tr>";
while ($row = $result->fetch_assoc()) {
  echo "<tr><td>" . $row['email'] . "</td><td>" . $row['count'] . "</td></tr>";
}
echo "</table>";

//create a php object named User with an id, name, and email property
class User
{
  public $id;
  public $name;
  public $email;
}

//create a function that takes a mysqli result object and returns an array of User objects
function result_to_users($result)
{
  $users = array();
  while ($row = $result->fetch_assoc()) {
    $user = new User();
    $user->id = $row['id'];
    $user->name = $row['name'];
    $user->email = $row['email'];
    $users[] = $user;
  }
  return $users;
}

// find all users with duplicate emails and print their data in a table
$users = result_to_users($mysqli->query("SELECT * FROM users WHERE email IN (SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1)"));
echo "<table border='1'><tr><th>ID</th><th>Name</th><th>Email</th></tr>";
foreach ($users as $user) {
  echo "<tr><td>" . $user->id . "</td><td>" . $user->name . "</td><td>" . $user->email . "</td></tr>";
}
echo "</table>";

foreach ($users as $user) {
  if ($user->name == "") {
    $id = $user->id;
    $mysqli->query("DELETE FROM users WHERE id = $id");
  }
}