Select from two different tables with same fieds name

Hi everybody,
so excited for my first post on this forum .

I am Trying to implement a select and join using the Active Record class from two tables, conversations and users, where conversations.user = users.id.

So I compose the query on this way:

$this->db->from('conversations');
$this->db->where('recipients',$id);
$this->db->join('users', 'conversations.user = users.id');
$query = $this->db->get();
return $query->result();

The result is fine I get all the row I need, but, problems comes when I use the data on the view, cause after that if I try to print the conversation id I get the user id instead.

<?= $message->id ?> --> Here is the user id instead of the conversation id.

Looks like the last id replaces the first   .

Can somebody show me ho to compose the query for getting both the two ids but with different identifier ?
So I may get them on my view writing something like that:

<?= $message->id ?> <?= $message->iduser ?>

limailmirino  

So I compose the query on this way:

$this->db->from('conversations');
$this->db->where('recipients',$id);
$this->db->join('users', 'conversations.user = users.id');
$query = $this->db->get();
return $query->result();

Can somebody show me ho to compose the query for getting both the two ids but with different identifier ?

Hi, and welcome to the forum!

If you are composing the query just like you say, you are getting all the fields in your query. If you don't specify $this->db->select('field 1', 'field 2', etc.), it's like your query has SELECT *. You can verify that by putting var_dump ($message); somewhere in your code. You should then see exactly what name is used for the conversation id. It is probably 'conversations.id.' You can then access that with $message->conversations.id;

This is just my personal preference. I use Active Record, but I do not use the query composition methods. Why? Because I think it's better (and just has hard) to learn the proper SQL. Then you can use $this->db->query('SQL query string'); One advantage is that you can test your SQL query string with PHPMyAdmin or any other SQL administrations software.

Hey, don't work without a PHP debugger. Several free IDEs have this features built in. Two are NetBeans and CodeLobster. Without a debugger, it's like you're driving with a blindfold on -- you are going to crash!

The problem you have is that the column 'id' exists in both of you tables. Since both has the same name, mysql only return one, the users ID in this case since that is that 'last' database it reads from.

What you have to do is to give both, or just one of them a unique name. Here is an example.

PHP Code:

$this->db->select('
conversations.id,
users.id AS user_id
');
$this->db->from('conversations');
$this->db->where('recipients',$id);
$this->db->join('users', 'conversations.user = users.id');
$query = $this->db->get();
return $query->result(); 


In the above example, I have given the users ID a new name, user_id, instead if just, id. By doing so it wont overwrite the conversations id.
Please note, you will have to specify any field you want to be included, the above example would only return id and user_id and nothing else.

Hi guys,
thanks a lot to both of you!

Siletium, you exactly got my problem and I solved it following your instruction.

Robert, I will do as you suggested writing the plain SQL query I actually rather this method instead of the composition class, even if I enjoyed using it for a few methods.

Bye bye guys!

See you on the next thread