[FIXED] $wpdb->get_results query returns different (wrong) results compared to phpmyadmin

Issue

I have 4 custom tables in my database - wp_api_teams, wp_api_matches, wp_api_competitions and wp_api_federations. They are part of my Wordpress database, this is how they look (I'm using foreign keys and all of that):

I am trying to output all the teams that are part of a federation. When I run this in phpmyadmin it works:

SELECT
    wp_api_teams.team_id,
    wp_api_teams.title,
    wp_api_competitions.comp_id,
    wp_api_competitions.title,
    wp_api_federations.federation_id,
    wp_api_federations.title
FROM
    wp_api_teams
JOIN wp_api_matches
    ON wp_api_teams.team_id = wp_api_matches.hometeam_id OR wp_api_teams.team_id = wp_api_matches.awayteam_id
JOIN wp_api_competitions
    ON wp_api_matches.comp_id = wp_api_competitions.comp_id
JOIN wp_api_federations
    ON wp_api_competitions.federation_id = wp_api_federations.federation_id
WHERE wp_api_federations.federation_id = 1
GROUP BY (wp_api_teams.team_id);

I get the exact results that I am looking for.

-----------------------------------------------------------------------------------
| team_id | title              | comp_id | title          | federation_id | title |
  1         Arsenal              1         Premier League   1               England
  2         Chelsea              1         Premier League   1               England
  3         Liverpool            1         Premier League   1               England
  4         Manchester United    1         Premier League   1               England
  5         Manchester City      1         Premier League   1               England

This is the code I'm using in Wordpress:

global $wpdb;
$sql = "
SELECT
    wp_api_teams.team_id,
    wp_api_teams.title,
    wp_api_competitions.comp_id,
    wp_api_competitions.title,
    wp_api_federations.federation_id,
    wp_api_federations.title
FROM
    wp_api_teams
JOIN wp_api_matches
    ON wp_api_teams.team_id = wp_api_matches.hometeam_id OR wp_api_teams.team_id = wp_api_matches.awayteam_id
JOIN wp_api_competitions
    ON wp_api_matches.comp_id = wp_api_competitions.comp_id
JOIN wp_api_federations
    ON wp_api_competitions.federation_id = wp_api_federations.federation_id
WHERE wp_api_federations.federation_id = 1
GROUP BY wp_api_teams.team_id
";
$test = $wpdb->get_results($sql);
echo('<pre>');
print_r($test);
echo('</pre>');

and the result that I'm getting is missing two table columns:

Array
(
    [0] => stdClass Object
        (
            [team_id] => 1
            [title] => England
            [comp_id] => 1
            [federation_id] => 1
        )
    [1] => stdClass Object
        (
            [team_id] => 2
            [title] => England
            [comp_id] => 1
            [federation_id] => 1
        )

.... and so on.

As you can see, the wp_api_teams.title and wp_api_competitions.title columns are missing from the result.

So my questions is, why is this happening, why are these columns being omitted and is there anything I can do to fix this, because I absolutely need them in my returned result. How can the same query produce a different result in WP vs PHPmyadmin.

Is this a known bug or is there something wrong with my code?

I'm running 10.2.14-MariaDB on localhost and all the table engines are InnoDB.

Solution

Your column names are the same in your result set. You need to provide an alias:

global $wpdb;
$sql = "
SELECT
    wp_api_teams.team_id,
    wp_api_teams.title as team_title,
    wp_api_competitions.comp_id,
    wp_api_competitions.title as competition_title,
    wp_api_federations.federation_id,
    wp_api_federations.title as federation_title
FROM
    wp_api_teams
JOIN wp_api_matches
    ON wp_api_teams.team_id = wp_api_matches.hometeam_id OR wp_api_teams.team_id = wp_api_matches.awayteam_id
JOIN wp_api_competitions
    ON wp_api_matches.comp_id = wp_api_competitions.comp_id
JOIN wp_api_federations
    ON wp_api_competitions.federation_id = wp_api_federations.federation_id
WHERE wp_api_federations.federation_id = 1
GROUP BY wp_api_teams.team_id
";
$test = $wpdb->get_results($sql);
echo('<pre>');
print_r($test);
echo('</pre>');

Answered By - JNevill