Welcome Guest, Not a member yet? Register   Sign In
wrong id returned in join
#1

(This post was last modified: 01-06-2016, 01:00 PM by frocco.)

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, 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
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, 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.
Reply
#6

(This post was last modified: 01-06-2016, 01:31 PM by sv3tli0.)

(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.
Best VPS Hosting : Digital Ocean
Reply
#7

(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
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

(This post was last modified: 01-07-2016, 05:38 AM by sv3tli0.)

(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..
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




Theme © iAndrew 2016 - Forum software by © MyBB