CodeIgniter Forums
What's the best way to determine if two tables were updated successfully using Active Record? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: What's the best way to determine if two tables were updated successfully using Active Record? (/thread-32824.html)

What's the best way to determine if two tables were updated successfully using Active Record? - El Forum - 08-05-2010

[eluser]rvillalon[/eluser]
So it seems that Active Record doesn't currently support JOIN with UPDATE. So, I've had to resort to updating one table at a time. But is there a better way to structure this?

I have a function the returns a BOOLEAN if the update was successful or not. However, I can't determine if both tables were updated successfully, only the last one it seems.

Any thoughts?

Code:
function update_company_by_userid($user_id, $data)
{
    $data1 = array(
        'name' => $data['name'],
        'website_name' => $data['website_name'],
        'website_url' => $data['website_url'],
        'phone' => $data['phone'],
        'fax' => $data['fax']
    );

    $this->db->where('user_id', $user_id);
    $this->db->update($this->company_profiles_table_name, $data1);
    
    $data2 = array(
        'address1' => $data['address1']
    );

    $this->db->where('company_id', $this->_get_company_id($user_id));
    $this->db->update($this->company_address_table_name, $data2);
    
    if ( ! $this->db->affected_rows() || $this->db->affected_rows() > 0) return TRUE;
    return FALSE;
}


What's the best way to determine if two tables were updated successfully using Active Record? - El Forum - 08-06-2010

[eluser]Benito[/eluser]
This will always return TRUE so I don't really see the point of:
Code:
! $this->db->affected_rows() || $this->db->affected_rows()
Why don't you simply check if company profile was updated/inserted, and then only insert company address if it was successful.
Your other option can be using transactions but this is only available if using InnoDB or BDB, but I think that would be an overkill.

What's the best way to determine if two tables were updated successfully using Active Record? - El Forum - 08-06-2010

[eluser]danmontgomery[/eluser]
Code:
$ret1 = $this->db->update('table1', $data1);
$ret2 = $this->db->update('table2', $data2);

if($ret1 && $ret2)
    return TRUE;

return FALSE;


What's the best way to determine if two tables were updated successfully using Active Record? - El Forum - 08-06-2010

[eluser]Jondolar[/eluser]
There may be a problem with noctrum's solution in that if the first update is successful and the second update is not successful, you may have an orphaned record. It depends on your requirements so if adding the first record without adding the second record is okay, then the above solution will work. Otherwise, you'll need to remove the first record before returning false or use a database transaction.

What's the best way to determine if two tables were updated successfully using Active Record? - El Forum - 08-27-2010

[eluser]LeonardoGaiero[/eluser]
I hope nobody minds if I post this a bit late, but could I get some insight over this method? I noticed that the active record lib returns the query if there was no error, so I thought of something like this:

Code:
function insertData($table, $data) {
    if($this->db->insert($table, $data) === FALSE)
        return FALSE;
    else
        return TRUE;
}

My problem is: will the DB function always return the query only on success, or is there a risk that it might return an unhandled query as opposed to boolean false? Is checking this value a foolproof way to determine a query's success? (edited for code coherence.)

What's the best way to determine if two tables were updated successfully using Active Record? - El Forum - 08-27-2010

[eluser]mddd[/eluser]
On "select" type queries, the query object is returned. That's how you can chain it with result methods:
Code:
$this->db->get('table')->result();
On "write/update/delete" type queries, true or false is returned, indicating the succesful execution.

Note: this does NOT necessarily mean the database was altered. Consider this query:
Code:
update table set status='active' where id=10
As long as the table exists and it has a field 'active' this will always return true. Even if there is no record with id=10. So in this case you would have to check db->affected_rows() to be sure the database really changed.

What's the best way to determine if two tables were updated successfully using Active Record? - El Forum - 08-27-2010

[eluser]LeonardoGaiero[/eluser]
[quote author="mddd" date="1282914563"]On "select" type queries, the query object is returned. That's how you can chain it with result methods:
Code:
$this->db->get('table')->result();
On "write/update/delete" type queries, true or false is returned, indicating the succesful execution.

Note: this does NOT necessarily mean the database was altered. Consider this query:
Code:
update table set status='active' where id=10
As long as the table exists and it has a field 'active' this will always return true. Even if there is no record with id=10. So in this case you would have to check db->affected_rows() to be sure the database really changed.[/quote]

Thanks for your reply, that sounds like a reasonable solution which I didn't think about (please bear with me, I'm still familiarizing with CI's multitude of functions); however, I'm pondering an alternative solution since since I have to use transactions anyway, because the task requires me to manually pass the user information to the database for auditing purposes (long story). So at this point my situation is much more similar to this:

Code:
function insertData($table, $data) {
    $this->db->trans_start();
//    todo: insert userdata query
    $this->db->insert($table, $data); // this is the main query; the DB will trigger an error and refuse to continue if userdata is empty
    $this->db->trans_complete();
    return ($this->db->trans_status());
}

The way I understand it, if the return value is false that means the transaction failed and it's been rolled back; at that point I'm under the impression it would be safe to use that value as a reliable indicator, and halt all subsequent instructions safely. Would there be any further issues in handling exceptions like this? My apologies for the mild OT.