Best Practices for Processing Errors from Database in CodeIgniter

Question

I have a table with a unique key (date + userid) in my web application database. When I try to insert record with existing date and userid I receive the following error:

dupicate key in table

I turned on the database debugger in application config, because I needed to use MySQL error numbers and messages. Now I need to process this error. I can use hard coded constants in the controller, but I think it's not good idea. I need database error handling in many places, and I dont like CodeIgniter's error handling. What is a best practice concerning the processing of database errors.

Answer 1

We use in our project constructions like these:

$this->db->_error_number();  
$this->db->_error_message();

but this undocumented functions and in next releases this could change. Of course you can simply use standard php for mysql error handle function:

mysql_errno()  
mysql_error()

internally CI use these functions.

As for me the best practice is use in custom Model's base class (BaseModel)

$this->db->_error_number();

and determine error, next throw exception with information error message taken from

$this->db->_error_message();

All your Model derived from BaseModel, and call method to check last request for db error and your Model must handle exception, and process it (may be additionally log), of course you can implement check result as result value and avoid of throwing exception.

Answer 2

I have a different suggestion for this i will recommend this

$this->form_validation->set_rules('email', 'Email', 'required|max_length[32]|valid_email|callback_email_available');

while submitting the form you need to define rules. always use callbacks to interact with database

Controller callback method

public function email_available($str)
{
    // You can access $_POST variable
    $this->load->model('mymodel');
    $result =   $this->mymodel->emailAvailability($_POST['email']);
    if ($result)
    {
        $this->form_validation->set_message('email_available', 'The %s already exists');
        return FALSE;
    }else{
        return TRUE;
    }
}

And model method

public function emailAvailability($email)
{
    $this->db->where('email',$email);
    $query  =   $this->db->get('tablename');
    return $query->row();
}

this way you will always avoid database errors on the front and can get user see things in a better way. you dont have to handle db errors because form validation is handling everything for you.