Problem with join

[eluser]Carmichael[/eluser]
Hi I have two tables. Matches and teams. In matches I've two columns where I store team id's. In teams I've one column where I store the name of the team and one where I store the team id.

matches

Code:

|match_id|match_team_home|match_team_away|
|   1    |       1       |       2       |
|        |               |               |

teams

Code:

|team_id|   team_name     |
|   1   |Manchester United|
|   2   |Manchester City  |

I want to get the team's name by the id's from the matches table.

Code:

function coming_matches_widget($limit)
   {
      $tt = $this->team_table;
      $mt = $this->match_table;
      
        $this->db->where('match_result', NULL);
        $this->db->join($tt, 'match_team_home = team_id', 'left');
        $this->db->join($tt.' AS b', 'match_team_away = b.team_id', 'left');
        $query = $this->db->get($mt, $limit);
        
if ($query->num_rows() > 0)
        {
            return $query->result_array();
        }else {
            return false;
        }
   }

That's the function I've been working on. It doesn't work right. I only get one value, not two different (team name's).

[eluser]Glazz[/eluser]
Maybe you need to use the select, something like:

Code:

->select('a.team_name as home_team, b.team_name as away_team')

You need to add the AS a to the first join table and change the ON condition aswell.

After that you will be able to use the home_team and away_team and 'echo' them where you want =)

[eluser]Carmichael[/eluser]
I figured it out.

Code:

function coming_matches_widget($limit)
    {
      $tt = $this->team_table;
      $mt = $this->match_table;
      
        $this->db->where($mt.'.match_result', '');
        $this->db->select("match_id, match_kickoff");
        $this->db->select("$tt.team_name as home_team_name, $tt.team_name_shorted as home_team_name_shorted, $tt.team_country_flag as home_team_country_flag");
        $this->db->select("team2.team_name as away_team_name, team2.team_name as away_team_name_shorted, team2.team_country_flag as away_team_country_flag");
        $this->db->join($tt, "$tt.team_id = $mt.match_team_home", "left");
        $this->db->join("$tt as team2", "team2.team_id = $mt.match_team_away", "left");
        $query = $this->db->get($mt, $limit);
            
  if ($query->num_rows() > 0)
        {
            return $query->result_array();
        }else {
            return false;
        }
    }

As you see the code is really ugly. Do I really have to rename all the columns in the query or is there some other technique I can use?

[eluser]Glazz[/eluser]
Using less queries, this is the only way i know.

But you can however, query the teams table first, and then when you loop through the matches you get the team name using the team id.

For example, you get the teams using something like:

Code:

$teams = $this->db->get('teams')->result_array();

And then you get all the matches:

Code:

$matches = $this->db->get('matches')->result();

Now where you want to show the matches data you create the "loop":

Code:

foreach ( $matches as $match ):
// lets show the name of the home team.
echo $teams[ $match->match_team_home ]['team_name'];
// show the name of the away team.
echo $teams[ $match->match_team_away ]['team_name'];
endforeach;

Something like this, don't know if it works because i didn't tested it, but this is the idea.