Welcome Guest, Not a member yet? Register   Sign In
What's the best way to determine if two tables were updated successfully using Active Record?
#1

[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;
}
#2

[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.
#3

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

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

return FALSE;
#4

[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.
#5

[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.)
#6

[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.
#7

[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.




Theme © iAndrew 2016 - Forum software by © MyBB