CodeIgniter Forums

Full Version: Update 2 tables simutaneously with CI3
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
I have two tables containing user information.  On occasion user profile information needs to be updated requiring a simultaneous update of both tables.  I was able to do this in CI2 using this code;

PHP Code:
   function update_linked ($id$data)
 
   {
 
       $this->db->set($data);
 
       $this->db->where('a.id'$id);
 
       $this->db->where'b.user_id'$id );       
        $this
->db->update('users AS a, user_profiles AS b');
 
    
$data is an array containing.  This generates this query which looks correct to me and it worked in CI2.

UPDATE `users AS a, user_profiles` AS `b` SET `a`.`username` = 'Test1', `a`.`email` = '[email protected]', `a`.`activated` = '1', `a`.`banned` = NULL, `a`.`ban_reason` = 'blub bug\r\n ', `a`.`group_id` = '300', `b`.`given_name` = 'Tester1', `b`.`surname` = 'Test1', `b`.`organization` = 'bug blat' WHERE `a`.`id` = '10' AND `b`.`user_id` = '10'

When I try this with CI3 I get
              Table 'model.users as a, user_profiles' doesn't exist

This leads me to believe there is something I am not understanding about how CI3 is doing DB updates.  Anyone have any suggestions?
The way active record/query builder checks for aliases in the first argument to $this->db->update() never took into account the possibility that multiple aliased tables would be passed in as a string.

It might work if you pass an array, but I really don't know without doing some testing:
Code:
$this->db->update(array('users as a', 'user_profiles as b'));
No joy on your idea. I could save the data as 2 arrays instead of one and run the update 2x but that seemed to me to be a waste of DB calls. Well, hopefully there will be many more ideas.
Seeing your error (Table 'model.users as a, user_profiles' doesn't exist), I think that your update() method is not the native one, because it adds "model." just before the table name. Is your model extending a MY_Model?
That model extends a CI_Model.  I was thinking, possibly incorrectly, that name is the database name which is model.  However as I think about your comment it looks like CI is looking for a table called 'users a as, user_profiles' which of course does not exist so the 'model' likely is the referring model.  This
PHP Code:
print_r($this->db->get_compiled_update('users AS a, user_profiles AS b')); 
will generate the query in my original post.
Honestly, if possible, I would try eliminating the aliases. Of course, if they're used in $data, that could be a significant task in itself.