Preventing Duplicate Records in PHP and MySQL

Other Questions MySQL
1

I’m working on a PHP and MySQL project and I’m trying to figure out the best way to stop duplicate entries from being added to my database. At first, I thought using a UNIQUE field in the table would be the most efficient solution. This way, MySQL would handle the duplicate check for me, and I’d only need one query instead of two.

But I’ve run into a problem. When there’s a duplicate entry, it causes an internal server error that I can’t seem to handle in my PHP code. I’m not sure what to do now.

Does anyone have experience with this? What’s the best approach to prevent duplicate records in a PHP and MySQL setup? I’m open to different ideas or techniques that might work better. Maybe there’s a way to catch and handle the error, or a different method altogether?

Here’s a simple example of what I’m trying to do:

function addUser($username, $email) {
    $query = "INSERT INTO users (username, email) VALUES (?, ?)";
    $stmt = $db->prepare($query);
    $stmt->execute([$username, $email]);
    // This fails if username or email already exists
}

Any suggestions would be really helpful. Thanks!

2

I’ve been in your shoes, Sophia. One approach that’s worked well for me is using a transaction with a try-catch block. This method allows you to attempt the insert and gracefully handle any duplicate key errors:

function addUser($username, $email) {
    global $db;
    try {
        $db->beginTransaction();
        $query = "INSERT INTO users (username, email) VALUES (?, ?)";
        $stmt = $db->prepare($query);
        $stmt->execute([$username, $email]);
        $db->commit();
        return true;
    } catch (PDOException $e) {
        $db->rollBack();
        if ($e->getCode() == '23000') {
            // Duplicate entry
            return false;
        }
        // Other database errors
        throw $e;
    }
}

This way, you maintain your UNIQUE constraint, catch any duplicate entry errors, and can handle them without causing a server error. It’s clean, efficient, and gives you full control over error handling.

3

I’ve encountered this issue before, and while henryg’s suggestion is valid, there’s another approach worth considering. You can use INSERT … ON DUPLICATE KEY UPDATE statement. This allows you to attempt the insert and handle duplicates in a single query. Here’s how you might modify your function:

function addUser($username, $email) {
    $query = "INSERT INTO users (username, email) VALUES (?, ?)
              ON DUPLICATE KEY UPDATE id = id";
    $stmt = $db->prepare($query);
    $stmt->execute([$username, $email]);
    return $stmt->rowCount() > 0;
}

This method is efficient as it requires only one database call. It will insert a new record if it doesn’t exist, or do nothing if it does. The function returns true for new insertions and false for duplicates. Remember to handle the return value appropriately in your code.

4

hey sophia, i’ve dealt with this before. instead of relying on UNIQUE constraints, you could do a SELECT query first to check if the record exists. if it doesn’t, then do the INSERT. it’s a bit slower but gives you more control. something like:

if (!userExists($username, $email)) {
    addUser($username, $email);
} else {
    echo "user already exists";
}

hope this helps!