• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
wrong id returned in join

#1
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
Reply

#2
Try

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

#3
(01-06-2016, 02: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
Reply

#4
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
Reply

#5
(01-06-2016, 02: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.
Reply

#6
(01-06-2016, 02:20 PM)frocco Wrote:
(01-06-2016, 02: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.
Best VPS Hosting : Digital Ocean
Reply

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

#8
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.
Reply

#9
(01-07-2016, 06: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..
Best VPS Hosting : Digital Ocean
Reply

#10
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 :-)
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


Users browsing this thread:
1 Guest(s)


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.