0

I have the following function:

public function getEventsBySportId(int $id)
{
    $query = $this->pdo->prepare(
        '
SELECT * 
  FROM calendar.events a
  JOIN sports b 
    ON a._sport_id = b.id 
  JOIN teams c 
    ON a._home_id = c.id 
  JOIN teams d 
    ON a._guest_id = d.id
'
    );
    $query->execute();
    $result = $query->fetchAll(\PDO::FETCH_OBJ);
    echo '<pre>';
    var_dump($result);
    die;
}

This gives me the following output when called:

array(5) {
  [0]=>
  object(stdClass)#6 (8) {
    ["id"]=>
    string(1) "2"
    ["date_and_time"]=>
    string(19) "2020-01-01 08:00:00"
    ["_sport_id"]=>
    string(1) "1"
    ["_home_id"]=>
    string(1) "1"
    ["_guest_id"]=>
    string(1) "2"
    ["notes"]=>
    string(12) "Foo bar baz."
    ["sport_name"]=>
    string(11) "Calvin Ball"
    ["team_name"]=>
    string(15) "Wyoming Waiters"
  }
  [1]=>
  object(stdClass)#7 (8) {
    ["id"]=>
    string(1) "3"
    ["date_and_time"]=>
    string(19) "2020-02-01 09:00:00"
    ["_sport_id"]=>
    string(1) "2"
    ["_home_id"]=>
    string(1) "2"
    ["_guest_id"]=>
    string(1) "3"
    ["notes"]=>
    string(13) "Baz qux quux."
    ["sport_name"]=>
    string(6) "Hockey"
    ["team_name"]=>
    string(12) "Utah Umbrage"
  }
...

My problem is that I want to get both the home team name and the guest team name for each event. Right now, both team names are assigned to one team_name key, with the guest team overwriting the home team.

Is there a way I can get both team names back, with separate names?

Strawberry
  • 33,750
  • 13
  • 40
  • 57
  • 2
    Remove asterisk from `SELECT * FROM` and specify only columns which you need. Add unique alias to each column. – Akina Mar 15 '20 at 10:28
  • 1
    Indeed, NEVER use `SELECT *` – Strawberry Mar 15 '20 at 10:30
  • Something like `SELECT a.*, c.team_name AS home_team, d.team_name AS guest_team ...` (ideally you should enumerate all the columns in `a` as well...) – Nick Mar 15 '20 at 10:38

0 Answers0