CodeIgniter Forums
wrong id returned in join - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Development (https://forum.codeigniter.com/forumdisplay.php?fid=6)
+--- Forum: CodeIgniter 3.x (https://forum.codeigniter.com/forumdisplay.php?fid=17)
+--- Thread: wrong id returned in join (/showthread.php?tid=64044)

Pages: 1 2

wrong id returned in join - frocco - 01-06-2016

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

RE: wrong id returned in join - PaulD - 01-06-2016

Try

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


RE: wrong id returned in join - frocco - 01-06-2016

(01-06-2016, 01:03 PM)PaulD Wrote: 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

RE: wrong id returned in join - sv3tli0 - 01-06-2016

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

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


RE: wrong id returned in join - frocco - 01-06-2016

(01-06-2016, 01:11 PM)sv3tli0 Wrote: 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.

RE: wrong id returned in join - sv3tli0 - 01-06-2016

(01-06-2016, 01:20 PM)frocco Wrote:
(01-06-2016, 01:11 PM)sv3tli0 Wrote: 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.

RE: wrong id returned in join - frocco - 01-06-2016

(01-06-2016, 01:25 PM)sv3tli0 Wrote:
(01-06-2016, 01:20 PM)frocco Wrote:
(01-06-2016, 01:11 PM)sv3tli0 Wrote: 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

RE: wrong id returned in join - PaulD - 01-07-2016

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.

RE: wrong id returned in join - sv3tli0 - 01-07-2016

(01-07-2016, 05:23 AM)PaulD Wrote: 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. Smile

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

RE: wrong id returned in join - PaulD - 01-07-2016

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 :-)