Active record query result doesn´t show all the fields with table joins

Question

I have the following sql query which when run from the console perfectly generates the result I want

SELECT * 
FROM tbl_fixtures f 
LEFT JOIN tbl_countries h 
ON f.home_team = h.country_id 
LEFT JOIN tbl_countries a 
ON f.away_team = a.country_id;

But when I run it from Codeigniter with the normal $this->db->query($sql)->result_array() I get only the last column of the team i.e. I get no h.country_id but only country id for the away team (which i am assuming it is replacing the first team). Could this be because of the f. a. and h. concats?. Can you please help on this query.

This is the var_dump of one result, because there is a lot of data but they are the same:

[0]=>
  array(8) {
    ["matchid"]=>      string(1) "1"
    ["home_team"]=>    string(1) "1"
    ["away_team"]=>    string(1) "2"
    ["date"]=>         string(19) "2016-06-10 21:00:00"
    ["country_id"]=>   string(1) "2"
    ["country_code"]=> string(2) "RO"
    ["country_name"]=> string(7) "Romania"
    ["group"]=>        string(1) "A"

Emmm, could you do a var_dump of your query results, and post it? I guess that due you´re doing a LEFT JOIN, if no row is there, you won´t get any result (Only NULL values). — Federico J., May 22 '16 at 10:52
No everything is filled and when i run the query from terminal is gives me everything. Var dump posted as requested — Combinu, May 22 '16 at 10:55
I´d try to do a ´$this->db->last_query();´ after that, and run the SQL that CI gives you, just to check what SQL is executing. Other option, is launching result() method instead of ´result_array()´, to check. — Federico J., May 22 '16 at 11:01

score 2 · Accepted Answer · edited May 23 '17 at 10:28

This is a guess:

You are doing a select *, so since all of your countries are in the same table and will have the same column name, you will get home or away column back when it hits CI. In this case, it looks like it is away. This is because the value will get overwritten in php as you are in effect giving the same associative key array the same value twice, because mysql is kind enough to let you do a SELECT * with clashing column names, and which is why you get your result in your console or mysql editor.

$dbResult['id_country'] = 1;
$dbResult['id_country'] = 2;

If you explicitly name your columns in the select clause, you can pick the correct country in your script.

SELECT *,
h.country_id as home_country_id,
f.country_id as away_country_id
FROM tbl_fixtures f 
LEFT JOIN tbl_countries h 
ON f.home_team = h.country_id 
LEFT JOIN tbl_countries a 
ON f.away_team = a.country_id;

I have left the select star in here so the rest of your script will work, but you should aim to query out everything you need, because of complications like this arising.

Additional reading on select *: Why is SELECT * considered harmful?

I think you´re write, is he´d give alias to the fields, probably he´ll get all of them, XD — Federico J., May 22 '16 at 11:04
Well it is a good guess that was my exact problem and i should learn that when not needed * should not be used! Thanks for the help. Fixed! — Combinu, May 22 '16 at 11:10

score 0 · Answer 2 · answered Aug 10 '16 at 01:59

I have the issue same this your. And my answer like Paul Stanley, only diference you use syntax Regular Queries $this->db->query($sql_query) If you used query builder class in codeigniter 3 then it will write look like this:

$this->db->select('*');
$this->db->select('h.country_id as home_country_id');
$this->db->select('a.country_id as away_country_id');
$this->db->join('tbl_countries as h', 'f.home_team = h.country_id', 'LEFT');
$this->db->join('tbl_countries as a', 'f.away_team = a.country_id', 'LEFT');
$query = $this->db->get('tbl_fixtures as f');
//var $object_reslut is array[object]
$object_result = $query->result();

if var_dump display $object_result:

0]=>
  array(8) {
    ["matchid"]=>      string(1) "1"
    ["home_team"]=>    string(1) "1"
    ["away_team"]=>    string(1) "2"
    ["home_country_id"] => string(1) "1"
    ["away_country_id"] => string(1) "2"
    ["date"]=>         string(19) "2016-06-10 21:00:00"
    ["country_code"]=> string(2) "RO"
    ["country_name"]=> string(7) "Romania"
    ["group"]=>        string(1) "A"

Active record query result doesn´t show all the fields with table joins

2 Answers2