Check if db->update successful with Codeigniter when potentially no rows are updated

Question

I've been using $this->db->affected_rows() to check if updates have been successful. But this doesn't work when a user inputs the same data to be stored that is already stored (because no column is actually updated). My workaround has been to get the stored data, compare to the inputted data, update if different, return a NO_CHANGE constant if same. With JSON data, there's an extra parsing step.

Is there an easier way to check that there was no error with the update? As in, something that doesn't require getting the stored data beforehand?

Lea

Does this answer your question? How can I detect a create, update, delete query is successful in Codeigniter – Yardstick

Answer 1

If I understand correctly, you can use transactions to ensure that there was no error with the update:

$this->db->trans_start();
$this->db->query('AN SQL QUERY...');
$this->db->update('table',$array);
$this->db->trans_complete();

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

Just remember this only validates that there were no SQL errors during all the queries inside the transaction.

Here's the link for more info Code Igniter Active Record Transaction

Terrify

This is very helpful, using $this->db->affected_rows() >=0 will return false when no new data is submitted. This suggestion solves this issue for me. Thank you! – Fruitless

Answer 2

Mirov When we are working with codeigniter, the data only updated when there is some change in the input field's value and then the $this->db->affected_rows() will return value greater then 0.

Suppose we have two field name and email, if we try to submit the form without changing any of the field then $this->db->affected_rows() will return 0 else it will return 1.

so better approach is to use

if($this->db->affected_rows() >=0){
  return true; //add your code here
}else{
  return false; //add your your code here
}

Relativity

Shouldn't this be $this->db->affected_rows() > 0 then? – Typecase

so if there is update what should show 1 or 0, just confused about it – Villasenor

@ Freddy Sidauruk it should be greater than 0, i have used >= 0 because some of the times the user open some edit panel and hit the update button without changing any thing in the panel. In that case i will use =0 and in other case i'll be using >= 0. Hope you have my point. – Relativity

great answer if you don't want to write some extra lines of code i.e. transactions.... – Redfield

Answer 3

Additionaly to the accepted answer solution you could expand the code like the following to differentiate affected_rows and trans_status results/errors.

$this->db->trans_start();
$this->db->query('AN SQL QUERY...');
$this->db->update('table',$array);
$this->db->trans_complete();
// was there any update or error?
if ($this->db->affected_rows() == '1') {
    return TRUE;
} else {
    // any trans error?
    if ($this->db->trans_status() === FALSE) {
        return false;
    }
    return true;
}

Tonnage

Answer 4

my "workaround" which is pretty easy -- is to include a field that is always updated. for example if you have a field and update it with a date & time value with seconds -- then the date time seconds will always be different even if the other update values are still the same.

bonus is you have a date time in the db record for when the last update was done.

Quadrille

Answer 5

I am not sure why we can't just use this:

if ($this->db->update('table',$array) === FALSE){
    // generate an error... or use the log_message() function to log your error
}

Leukemia

because there is a difference between a query that had no syntax errors & affected no rows versus a query that had no syntax errors & successfully affected rows. – Yardstick

Answer 6

CodeIgniter syntax 4:

$db->transStart();
$db->query('AN SQL QUERY...');
$db->update('table',$array);
$db->transComplete();

if ($db->transStatus() === FALSE)
{
    // generate an error
}

Agnesse