[Gazman Development]

Hey All,
   I have tried searching everywhere and none of the solutions seem to work.  I'm using CIv3 with a MySQL back end.  Below is a simple outline of the issue.

Table 1 - Character
PK: ID
Some Data Field
Foreign Key: party_id

Table 2 - Party
PK: ID
Some Data fields
user_id

So the character table links to the party table like so:
character.party_id = party.id

And I only want to update (or delete from) the Character table *IF* the supplied user ID to the model function matches the user_id in the Party table.

My MySQL is a little rusty, but in MSSQL it would look like:

Code:

update c
set c.<information>= <input data>
from character c
  inner join party p on c.party_id = p.id
where
c.id = <character_id>
and
p.user_id = <logged in user id>

Some solutions I've tried without success:
https://forum.codeigniter.com/thread-637...pdate+join
https://stackoverflow.com/questions/4830...pdate-quer
https://stackoverflow.com/questions/3197...using-join
https://stackoverflow.com/questions/9578...ith-a-join
https://developer-paradize.blogspot.com/...using.html

I really don't want to have to hand-write SQL code, nor do I want to do an initial "select" query to make sure the user is linked to the correct party to be able to update (or delete) this entry.

I'm sure I'm missing something simple with this.

[demyr]

What about this?

PHP Code:

public function find_party_user($user_id) {

$this->db->select('something')->from('party_table')
->where('user_id', $user_id);
$where_clause = $this->db->get_compiled_select();

$query = $this->db->delete('character_table', array('some_id' => $where_clause));
} 


get_compiled_select might be the answer for your problem.

[InsiteFX]

If that doe's not work you can try reading this.

MySQL Tutorial - MySQL DELETE JOIN

[Gazman Development]

Thanks for the responses. I was initially thrown by the delete part of things but can see where you're going with this. I work with joined updates and deletes all the time, but hadn't thought of using a nested select as the where clause in CI (was hoping CI could handle it within query builder).

It's been a crazy week at work and as this is a personal project, the last thing I want to do is spend more time wrestling with code. So I'll give it go later this week (hopefully!) and let you know.