How to recover from failed database query in CodeIgniter?

Question

In CodeIgniter, if your sql query fails, then the script stops running and you get an error. Is there any way to do it so you can try a query, and if it fails, then you silently detect it and try a different query without the user knowing that the query failed?

Answer 1

You could modify the Exceptions class to... throw an exception. Just create MY_Exceptions.php in application/core/:

class MY_Exceptions extends CI_Exceptions {

    function show_error($heading, $message, $template = 'error_general', $status_code = 500)
    {
        // BEGIN EDIT
        if ($template === 'error_db')
        {
            throw new Exception(implode("\n", (array) $message));
        }
        // END EDIT

        set_status_header($status_code);

        $message = '<p>'.implode('</p><p>', ( ! is_array($message)) ? array($message) : $message).'</p>';

        if (ob_get_level() > $this->ob_level + 1)
        {
            ob_end_flush();
        }
        ob_start();
        include(APPPATH.'errors/'.$template.'.php');
        $buffer = ob_get_contents();
        ob_end_clean();
        return $buffer;
    }
}

Then use a try/catch block to check for the error and attempt to run another query:

try {
    $this->db->get('table1');
} catch (Exception $e) {
    $this->db->get('table2');
}

It's kind of a sloppy workaround, but it gets the job done.

You might want to take a look at transactions as well:

Running Transactions

To run your queries using transactions you will use the $this->db->trans_start() and $this->db->trans_complete() functions as follows:

$this->db->trans_start();
$this->db->query('AN SQL QUERY...');
$this->db->query('ANOTHER QUERY...');
$this->db->query('AND YET ANOTHER QUERY...');
$this->db->trans_complete();

You can run as many queries as you want between the start/complete functions and they will all be committed or rolled back based on success or failure of any given query.

Answer 2

One of the ways to achieve this is

First.

Set  ['db_debug'] = FALSE; in config/database.php

Then,

In your model -

public function attempt_one($data) {
  //build your query ....
  $query_result = $this->db->insert('table_name');

  if(!$query_result) {
     $this->error = $this->db->_error_message();
     $this->errorno = $this->db->_error_number();
     return false;
  }
  return $something;
}

public function attempt_two() {
  //another query goes in here ...
}

in your controller -

public function someAction ()
{
  //some code 
  $data = $some_data;
  $result1 = $this->yourmodel->attempt_one($data);
  if($result1 === false)
  {
    //Some code to send an email alert that first query failed with error message 
    //and/or log the error message/ number 
    $result2 = $this->yourmodel->attempt_two($data);
  }

}