Update two joined tables

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

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

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

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

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

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

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

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

[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

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