Update 2 tables simutaneously with CI3 |
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) 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?
Website: http://avenir.ro
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'));
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.
|
Welcome Guest, Not a member yet? Register Sign In |