Codeigniter handle database error without num_rows()

Question

I'm having trouble finding a simple method for handling database errors in CI. For instance, I can't insert duplicate entries in my database table. If I try to, I get a 1062 database error.

The most common solution suggested is to check if the entry already exists and use

$query->num_rows() > 0

in a if-statement to prevent an error. That method seems redundant to me because I'm performing an extra query. Ideally I want to check if an error occurs in my main query or if a row is affected.

I found the following functions that may help

$this->db->affected_rows()

$this->db->_error_message()

however I'm not sure how to use them.

I tried in my Model:

$this->db->insert('subscription', $data);
return $this->db->affected_rows();

To my understanding that should return the number of effected rows. Then in my controller I added:

$affected = $this->Subscribe_model->subscribe($data);

if ($affected < 1)
{
    //display error message in view
}
else
{
   $this->Subscribe_model->subscribe($data); //perform query
}

Unfortunately the script stops in the model at $this->db->insert('subscription', $data); if an error occurs and displays the entire database error.

Ben · Accepted Answer · 2011-07-23 20:27:46Z

2

I do not know if this works for $this->db->insert();, but $this->db->query(); will return false if it errors so you could do something like this:

$sql = $this->db->insert_string('subscription', $data);
$sql = $this->db->query($sql)
if(!$sql){
    //Do your error handling here
} else {
    //query ran successfully
}

edited Jul 23, 2011 at 20:27

answered Jul 23, 2011 at 20:20

Ben

231 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

cwallenpoole · Accepted Answer · 2011-07-15 02:23:58Z

1

Try using @$this->db->insert('subscription', $data);, @, in PHP means "suppress warning".

As an alternate -- if you know that data is safe, or you're willing to use $this->db->insert_string, you could add, on duplicate key to the end of the query.

This should work (untested):

$this->db->simple_query( $this->db->insert_string( 'subscription', $data ) . 
                            ' ON DUPLICATE KEY ' . 
                            $this->db->update_string( 
                                        'subscription', 
                                         $data, 
                                         /* your where clause here */ );

answered Jul 15, 2011 at 2:23

cwallenpoole

82.4k26 gold badges132 silver badges174 bronze badges

3 Comments

CyberJunkie Over a year ago

Thanks! Would IGNORE in the query also be a good solution? I'm trying to keep db queries minimal, should a duplicate row already exist.

cwallenpoole Over a year ago

Sure, I would just do str_replace( 'INSERT', 'INSERT IGNORE', $this->db->insert_string( 'subscription', $data ) );

cwallenpoole Over a year ago

You can also use query for this. simple_query, while useful for times when you want minimal processing, does not do as much as query does.

Collectives™ on Stack Overflow

Codeigniter handle database error without num_rows()

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related