CodeIgniter catching DB error message (Currently returns 0 if dbdebug is false)

Question

When I execute this query:

public function delete_user_test($id){
        $this->db->trans_begin();
        $this->db->where('id',$id);
        $this->db->delete($this->user_table);
        $person_id = $this->get_user_account($id)['person_id'];
        $this->db->where('id',$person_id);
        $this->db->delete($this->person_table);
        $this->db->trans_complete();
        if($this->db->trans_status()===false){
            $this->db->trans_rollback();
            return $this->db->error();
        }else{
            $this->db->trans_commit();
            return true;
        }
    }

and dbdebug in the database config file is set to true, I will get a full screen error saying

Error Number: 1451

Cannot delete or update a parent row: a foreign key constraint fails (`database_table`.`acc_codes`, CONSTRAINT `acc_codes_fk` FOREIGN KEY (`user_id`) REFERENCES `user_accounts` (`id`))

DELETE FROM `user_accounts` WHERE `id` = '2'

Filename: models/AccMdl.php

Line Number: 655

How do I get the error message from that page and pass it back in a banner instead of a full page? If I turn dbdebug false, all I get from $this->db->error(); is 0.

Edit

Added my database config stuff.

$db['default'] = array(
    'dsn'   => '',
    'hostname' => host,
    'username' => uname,
    'password' => pw,
    'database' => name,
    'dbdriver' => drvr,
    'dbprefix' => '',
    'pconnect' => FALSE,
//  'db_debug' => (ENVIRONMENT !== 'production'),
    'db_debug' => FALSE,
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_general_ci',
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE
);

Edit 2

I have managed to get the error out. The method I used was to remove trans_begin and the other transaction items. Is there a way to integrate this with the transaction items?

$this->db->where('id',$id);
        if(!$this->db->delete($this->user_table)){
            return $this->db->error();
        }

This will produce the error message. But how do I combine this with the rest?

Are you using PDO for the driver?

Wiguno
– Wiguno

2018-01-01 16:39:27 +00:00
Commented Jan 1, 2018 at 16:39 — Wiguno
– Wiguno, Commented Jan 1, 2018 at 16:39
Nope, I'm using mysql

JianYA
– JianYA

2018-01-01 16:50:15 +00:00
Commented Jan 1, 2018 at 16:50 — JianYA
– JianYA, Commented Jan 1, 2018 at 16:50
throw an Exception and catch an Exception

Alex
– Alex

2018-01-01 19:51:35 +00:00
Commented Jan 1, 2018 at 19:51 — Alex
– Alex, Commented Jan 1, 2018 at 19:51
but the db error is supposed to be exception handling

JianYA
– JianYA

2018-01-02 03:40:06 +00:00
Commented Jan 2, 2018 at 3:40 — JianYA
– JianYA, Commented Jan 2, 2018 at 3:40

DFriend · Accepted Answer · 2018-01-01 20:02:06Z

1

First, if you are attempting to manually manage transactions do not use $this->db->trans_complete(). It will call rollback() if there's a problem.

Second, don't attempt to manually manage transactions. Every case I've ever seen people write the same code that CodeIgniter already has - but usually not as cleanly. Why reinvent the wheel?

I would suggest having faith in automatic transactions.

function delete_user_test($id)
{
    $this->db->trans_start();
    $this->db
        ->where('id', $id)
        ->delete($this->user_table);

    $person_id = $this->get_user_account($id)['person_id'];  //huh???

    $this->db
        ->where('id', $person_id)
        ->delete($this->person_table);

    $this->db->trans_complete();

    if($this->db->trans_status() === false)
    {
        // generate an error... 
        // or use the log_message() function to log your error
    }
}

You can try using $this->db->error(); if trans_status() === false, but know that it only reports the outcome of the last operation. (using 'mysqli' driver) So using this

$error = this->db->error();

The value of $error['code'] will likely be 0 because the last db operation (rollback) probably succeeded.

answered Jan 1, 2018 at 20:02

DFriend

8,9741 gold badge16 silver badges26 bronze badges

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

2 Comments

JianYA Over a year ago

Hi, thanks for replying. I removed the rollback and tested. I still get {"code":0,"message":""} when I shut off dbdebug. When its on, the huge error message appears.

JianYA Over a year ago

$person_id = $this->get_user_account($id)['person_id']; is another function. It doesn't matter much here as I am trying to get a db error for the sake of getting the error message.

ourmandave · Accepted Answer · 2018-01-01 18:44:10Z

0

$this->db->error() returns an array with 'error' and 'code' elements you can check.

https://www.codeigniter.com/userguide3/database/queries.html#handling-errors

So something like:

if($this->db->trans_status()===false){
    $this->db->trans_rollback();

    // Get the error array
    $error = $this->db->error();
    // Return the message - but that doesn't work with returning true/false.
    return $error('error');

} else { ...

answered Jan 1, 2018 at 18:44

ourmandave

1,5852 gold badges16 silver badges55 bronze badges

1 Comment

JianYA Over a year ago

it returns 0 for the error code and "" for the message each time though

JianYA · Accepted Answer · 2018-01-02 07:24:48Z

I have managed to solve it.

So I discovered that $this->db->error() works only immediately after the query itself and not in the trans_status area. A workaround I found for this was

public function delete_user_test($id){
        $this->db->trans_begin();
        $this->db->where('id',$id);
        $result = $this->db->delete($this->user_table);
        if(!$result){return $this->db->error();}
        $person_id = $this->get_user_account($id)['person_id'];
        $this->db->where('id',$person_id);
        $result = $this->db->delete($this->person_table);
        if(!$result){return $this->db->error();}
        $this->db->trans_complete();
        if($this->db->trans_status()===false){
            $this->db->trans_rollback();
        }else{
            $this->db->trans_commit();
            return true;
        }
    }

I'm keeping the rollback just in case but this works for me

Collectives™ on Stack Overflow

CodeIgniter catching DB error message (Currently returns 0 if dbdebug is false)

Edit

Edit 2

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Edit

Edit 2

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related