+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: SQL : join with tables with same fields name (eg primary id) return invalid results (/showthread.php?tid=78087)
SQL : join with tables with same fields name (eg primary id) return invalid results - Fred9176 - 11-27-2020
Hi,
I noticed that if I build a query with 2 tables having the same name for primary key (and I guess this will be the case with any other field), the result is invalid as the id key in the resulting array contains only the id of the joined table.
This is for example the case in the documentation :
PHP Code:
$builder->db->table('blog');
$builder->select('*');
$builder->join('comments', 'comments.id = blogs.id');
$query = $builder->get();
// Produces:
// SELECT * FROM blogs JOIN comments ON comments.id = blogs.id
We should have 2 id keys (I know this isn't possible in php, but the name of the result array key should be blogs.id and comments.id)
Sincerly,
Fred
RE: SQL : join with tables with same fields name (eg primary id) return invalid results - neoneeco - 11-27-2020
When there is a possible confusion of fields, it is then necessary to list them all in the SELECT with the name of the table first (i think)
RE: SQL : join with tables with same fields name (eg primary id) return invalid results - InsiteFX - 11-27-2020
This is in the forums here using search on How CodeIgniter join two tables to have both Id columns.
CodeIgniter Forums - How codeigniter join two tables to have both Id columns