wrong id returned in join

Hello,

Table1
 id
 table2_id
 name1

table_id = 14
Record id = 13

Table2
 id
 name2

Record id = 14

Code:

$this->db->join('Table2', 'Table2.id = Table1.table2_id');
$query=$this->db->get('Table1');

foreach ($records->result() as $row
echo $row->id

prints value of 14, not 13

Try

Code:

$this->db->from('Table1');
$this->db->join('Table2', 'Table1.table2_id = Table2.id');
$this->db->get();

Try

Code:

$this->db->from('Table1');
$this->db->join('Table2', 'Table1.table2_id = Table2.id');
$this->db->get();

Thanks, still getting id of 14 instead of 13

You should define the SELECT string not using the default which is '*' ..

PHP Code:

$this->db->select('Table1.id, Table1.name1, Table2.name2'); 


Best VPS Hosting : Digital Ocean

You should define the SELECT string not using the default which is '*' ..

PHP Code:

$this->db->select('Table1.id, Table1.name1, Table2.name2'); 


Thanks, that resolves it, but kind of a pain to have to code all db fields.

You should define the SELECT string not using the default which is '*' ..

PHP Code:

$this->db->select('Table1.id, Table1.name1, Table2.name2'); 


Thanks, that resolves it, but kind of a pain to have to code all db fields.

You can't skip that thing. When there are 2 'id' fields its normal that you get just 1 of them. (PHP restrictions..)

I am not quite sure but if you set 

PHP Code:

$this->db->select('Table2.*, Table1.*'); 


Perhaps you will get the duplicated keys from the second set table. 
I am not quite sure if you will be allowed to do that but you  can try it.

Best VPS Hosting : Digital Ocean

You should define the SELECT string not using the default which is '*' ..

PHP Code:

$this->db->select('Table1.id, Table1.name1, Table2.name2'); 


Thanks, that resolves it, but kind of a pain to have to code all db fields.

You can't skip that thing. When there are 2 'id' fields its normal that you get just 1 of them. (PHP restrictions..)

I am not quite sure but if you set 

PHP Code:

$this->db->select('Table2.*, Table1.*'); 


Perhaps you will get the duplicated keys from the second set table. 
I am not quite sure if you will be allowed to do that but you  can try it.

I tried that, same issue unless I code the select.

Thanks

It is strange because I have never had that issue despite using many joins with '*' as select criteria.

I assume that table 1 has id as a primary key, if not that might be the cause.

It is strange because I have never had that issue despite using many joins with '*' as select criteria.

I assume that table 1 has id as a primary key, if not that might be the cause.

I don't have the time to recreate the problem. But the problem comes within the PHP.

Mysql returns both ids from those 2 tables - id=13 and id=14.
But when PHP try to put them into PHP array(object) its not possible to keep them both so 1 of them is ignored and lost.
You know that $row['id'] can return just 1 value.

For that reason its good always to specify SELECT fields or to be sure that you got what you need..

Best VPS Hosting : Digital Ocean

Yes, in fact I would never have a column called 'id', I have 'user_id', 'letter_id', 'address_id' etc. etc. which is probably why I have never had this issue.

Also, using select fields is something I am trying to be stricter on with myself, as it minimizes array sizes too, which with multiple joins can become quite huge in size. But I have lots of bad coding habits, that I have only realized are bad coding habits with experience. Goodness knows what else I do wrong without realizing it yet :-)