[php] CodeIgniter Join Help

[Hightower]

I'm using CodeIgniter on a project and I need to do a strange kind of join. I can't get my head around it so can anyone help me with an Active Record join.

 

My Tables are like this:

 

FIXTRURES

|-------------|-------------|--------------|---------------|
|   id        |    date     |    home_id   |     away_id   |
|-------------|-------------|--------------|---------------|
|    1        |    20-10-10 |    1         |      2        |
|-------------|-------------|--------------|---------------|


TEAMS

|-------------|-------------|
|   id        |   name      |
|-------------|-------------|
|    1        |  team one   |
|-------------|-------------|
|    2        |  team two   |
|-------------|-------------|

 

I need to be able to join to the teams table twice to pluck the home_team and away_team so that the result might look like:

 

DATE >> 20-10-10
HOME_TEAM >> team one
AWAY_TEAM >> team two

 

I would greatly appreciate any code that will do this, but also to explain the code for me so I can understand how to think of this for myself would be even greater appreciated.

 

I've looked at some Google links but I really can't get my head around it.

 

Many thanks!

[ZeroHour]

Standard mysql would be:

SELECT * FROM fixtures INNER JOIN teams ON fixtures.home_id=teams.id INNER JOIN teams ON fixtures.away_id=teams.id

 

I think.

[webman]

I think you have to alias tables when joining the same one. E.g.

 

SELECT fixtures.*, h.name AS home_team, a.name AS away_team
FROM fixtures
LEFT JOIN teams h ON fixtures.home_id = h.id
LEFT JOIN teams a ON fixtures.away_id = a.id

[ZeroHour]

Its been a while since I have had to manually write a join, I have a mysql tool for that which is very nice and simple to use (but not free)

[Hightower]

I think you have to alias tables when joining the same one. E.g.

 

SELECT fixtures.*, h.name AS home_team, a.name AS away_team
FROM fixtures
LEFT JOIN teams h ON fixtures.home_id = h.id
LEFT JOIN teams a ON fixtures.away_id = a.id

 

I've translated that to this in Active Record:

 

$this->db->select('fixtures.*, h.name AS home_team, a.name AS away_team')
           ->from('fixtures')
           ->join('h','fixtures.home_team = h.id','left')
           ->join('a','fixtures.away_team = a.id','left');
       
       $query = $this->db->get();
       print_r($query->row());

 

Which produces this error:

 

		[b]A Database Error Occurred[/b]

		Error Number: 1146
Table 'fcms.h' doesn't exist
SELECT `fixtures`.*, `h`.`name` AS home_team, `a`.`name` AS away_team FROM (`fixtures`) LEFT JOIN `h` ON `fixtures`.`home_team` = `h`.`id` LEFT JOIN `a` ON `fixtures`.`away_team` = `a`.`id`

[CESIL]

I think this works

SELECT fixtures.date, home.name AS HomeTeam, away.name AS AwayTeam
FROM (fixtures INNER JOIN teams AS home ON fixtures.home = home.id) INNER JOIN teams AS away ON fixtures.away = away.id;

[webman]

That's because Active Record isn't intelligent enough to work out aliases. Run it is a string

[Hightower]

I think this works

SELECT fixtures.date, home.name AS HomeTeam, away.name AS AwayTeam
FROM (fixtures INNER JOIN teams AS home ON fixtures.home = home.id) INNER JOIN teams AS away ON fixtures.away = away.id;

 

I translated that to:

 

$this->db->select('fixtures.date, home.short_name AS hometeam, away.short_name AS awayteam')
           ->from('fixtures')
           ->join('teams AS home','fixtures.home_team = home.id','inner')
           ->join('teams AS away','fixtures.away_team = away.id','inner');
           
       $query = $this->db->get();
       print_r($query->row());

 

and it worked a treat. Thanks!

 

Any chance you could explain it to me?

[CESIL]

The key bit is using 'teams AS home' which creates the alias that webman mentioned.

 

It is the same technique as renaming the columns using 'AS' so that you van have a result column as home or away from a column called name.