How to Handle Duplicate Entry Errors in MySQL with CodeIgniter?

Other Questions MySQL
1

I’m working on a project using CodeIgniter and MySQL. I’ve set up a UNIQUE constraint on a field to stop users from adding duplicate data. It works fine most of the time, but when someone tries to add a duplicate entry, I get an internal server error.

I’m using $this->db->_error_message() to check for database errors, but it’s not catching this one. Instead, the program just stops and sends an error number back to my AJAX call.

Does anyone know a better way to deal with these duplicate entry errors? Am I missing something in my setup? I’d really like to handle this gracefully instead of showing users an internal server error.

Here’s a simplified version of what I’m doing:

function add_user($username) {
    $data = array('username' => $username);
    $this->db->insert('users', $data);
    if ($this->db->affected_rows() == 0) {
        // This doesn't catch the duplicate error
        $error = $this->db->_error_message();
        return array('success' => false, 'message' => $error);
    }
    return array('success' => true);
}

Any help would be much appreciated!

2

I’ve encountered similar issues with CodeIgniter and MySQL. One effective approach is to use database transactions. Wrap your insert operation in a transaction, then commit if successful or rollback if an error occurs. This way, you can catch and handle the duplicate entry error more reliably.

Here’s a basic example:

$this->db->trans_start();
$this->db->insert('users', $data);
$this->db->trans_complete();

if ($this->db->trans_status() === FALSE) {
    $error = $this->db->error();
    if ($error['code'] == 1062) {  // MySQL error code for duplicate entry
        return array('success' => false, 'message' => 'Username already exists');
    }
    // Handle other errors
}

return array('success' => true);

This method provides more control over error handling and maintains data integrity.

3

yo markseeker91, ive had this problem too. what worked for me was checking if the username exists before inserting. somethin like:

$exists = $this->db->get_where('users', array('username' => $username))->num_rows();
if ($exists) {
    return array('success' => false, 'message' => 'Username taken');
}

then do ur insert if it doesnt exist. this way u avoid the error altogether.

4

I’ve dealt with this exact issue in a recent project. What worked for me was using the db->error() method instead of _error_message(). It returns an array with both the error code and message, which is more reliable for catching specific errors like duplicates.

Here’s what I ended up doing:

function add_user($username) {
    $data = array('username' => $username);
    $this->db->insert('users', $data);
    
    if ($this->db->affected_rows() == 0) {
        $error = $this->db->error();
        if ($error['code'] == 1062) {
            return array('success' => false, 'message' => 'Username already exists');
        }
        return array('success' => false, 'message' => 'An error occurred');
    }
    
    return array('success' => true);
}

This approach lets you handle the duplicate entry error specifically, while still catching other potential database errors. It’s been working smoothly in my production environment for months now.

5

hey markseeker91, ive run into this before. try using try-catch blocks to handle the exception. something like:

try {
    $this->db->insert('users', $data);
} catch (Exception $e) {
    return array('success' => false, 'message' => 'Duplicate entry');
}

this should catch the error and let u handle it gracefully. hope it helps!