Mystic Jay Mystic Jay - 6 months ago 16
MySQL Question

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

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"

Answer

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?

Comments