Update two joined tables - Printable Version

Update two joined tables - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Update two joined tables (/showthread.php?tid=13182)

Pages: 1 2

Update two joined tables - El Forum - 11-13-2008

[eluser]insub2[/eluser]
I'm having a hard time finding out how to do this.

I can get from the DB with the join and it works as expected.
But I can't update using $this->db->update($table, $insert).

This works for getting:

Code:
function get_profile()

    {

        $this->db->get($this->_prefix.'user');

        $this->db->join($this->_prefix.'user', $this->_prefix.'user.id = '.$this->_prefix.'user_profile.id');

        //SELECT name FROM user WHERE id = $id 

        $this->db->where('user_name', 'insub2'); //I Know, I just haven't gotten to the point where this is done dynamically

        $query = $this->db->get($this->_table);

        $row = $query->row();

        return $row;

    }

I can't seem to find anyway to update a join or two tables in one query. Is it possible? It seems like it should be.

Update two joined tables - El Forum - 11-13-2008

[eluser]attos[/eluser]
It cannot be done with one query. You have to use two inserts/updates.
First apply the changes to the parent table and then the child.

You can also use views with some databases. In this case the DB engine takes care of the correct sequence of inserts/updates.

Update two joined tables - El Forum - 11-13-2008

[eluser]insub2[/eluser]
[quote author="attos" date="1226632455"]It cannot be done with one query. You have to use two inserts/updates.
First apply the changes to the parent table and then the child.

You can also use views with some databases. In this case the DB engine takes care of the correct sequence of inserts/updates.[/quote]

Thanks!

Update two joined tables - El Forum - 07-14-2009

[eluser]spmckee[/eluser]
I was having the same issue but solved it by running two separate queries.

But when I run

Code:
return ($this->db->affected_rows() > 0) ? true : false;

I can only get one feedback for table or the other, I can't get both tables at the same time. How can this be done?

Thanks.

Update two joined tables - El Forum - 07-15-2009

[eluser]Thorpe Obazee[/eluser]
You'd have to run affected_rows() twice. Run them immediately after each insert or update.

Update two joined tables - El Forum - 07-15-2009

[eluser]spmckee[/eluser]
Thanks bargainph but when I do that I only get results for the last one executed.

For instance, if I update something in the second query I get a result. But if I update something in the first--and not in the second--the "affected_rows" returns none.

Here's what I have:

Code:
// Users table.

        $userdata = array('username' => $username, 

                      'email' => $email,

                      'status' => $status);

        $this->db->where('id', $id);

        $this->db->join('meta', 'meta.id = users.id', 'left');

        $this->db->update('users', $userdata);

    // Meta table.

        $metadata = array('first_name' => $first_name, 

                      'last_name' => $last_name,

                      'my_feeds' => $my_feeds);

        $this->db->where('user_id', $id);

        $this->db->update('meta', $metadata);

         /**/

        return ($this->db->affected_rows() > 0) ? true : false;

Update two joined tables - El Forum - 07-15-2009

[eluser]Thorpe Obazee[/eluser]

Code:
// Users table.

        $userdata = array('username' => $username, 

                      'email' => $email,

                      'status' => $status);

        $this->db->where('id', $id);

        $this->db->join('meta', 'meta.id = users.id', 'left');

        $this->db->update('users', $userdata);

    // Meta table.

    // Mod

       $affected_rows1 = $this->db->affected_rows() ? TRUE : FALSE;

        $metadata = array('first_name' => $first_name, 

                      'last_name' => $last_name,

                      'my_feeds' => $my_feeds);

        $this->db->where('user_id', $id);

        $this->db->update('meta', $metadata);

         /**/

       // Mod

        $affected_rows2 = $this->db->affected_rows() ? TRUE : FALSE;

      // Mod

        return ($affected_rows1 AND  $affected_rows2) ? TRUE : FALSE;

Update two joined tables - El Forum - 07-15-2009

[eluser]spmckee[/eluser]
Brilliant! Thanks for the help. Much appreciated.

Update two joined tables - El Forum - 08-13-2009

[eluser]Nelson Mendes[/eluser]
Shouldn't the return statement be like this?

Code:
return ($affected_rows1 OR $affected_rows2) ? TRUE : FALSE;

Because, as far as I understand, you want to return TRUE if at least one of the tables was updated.

Using the operator AND will only return TRUE if both variables are TRUE

Update two joined tables - El Forum - 08-13-2009

[eluser]Johan André[/eluser]
If it is really crucial that both tables get updated, use transactions...