MySQL JOIN tables in Codeigniter

Question

I am trying to create a fixture for two teams in Codeigniter. I have a table for team

TEAM
team_id
team_name
team_logo

I also have a table for fixture.

Fixture
fixture_id
fixture_text
fixture_comp
fixture_type
fixture_level
fixture_date

The relationship between these two tables is many to many which creates a JOIN table which has a composite PK made up from team_id and fixture_id

Team_Fixtures
team_id
fixture_id

I am a beginner to sql and Codeigniter which is the framework I am working with and I am looking to query these tables. I want to get all the information from the fixture table plus the team name and logo from the team table. I am able to query the tables individually and get the information but am wondering how to write a join statement in Codeigniter that will get all the information in the same query.

This is what I use to query the fixture table at the moment.

MODEL

<?php  
class Fixture_model extends CI_Model {  


 public function __construct()
 {
  parent::__construct();
 }

 function fixtures() 

    {
        $fixture_level = "Roinn 1B";
        //Query the fixture table for every record and row  

        $results = array();
        $this->db->select('fixture_text, fixture_type, fixture_comp, fixture_date,   fixture_level');
        $this->db->from('fixture');
        $this->db->where('fixture_level', $fixture_level );

        $query = $this->db->get();

        if($query->num_rows() > 0) 
        {
         $results = $query->result();
        }

        return $results;   

    }  

}
/*End of file fixture_model.php*/
/*Location .application/models/fixture_model.php*/ 
?>

And in my VIEW

<?php

     if (is_array($results))
     {
       if( !empty($results) ) 
       {

         foreach($results as $row) 
         {

          echo '<div class="col-sm-6 col-md-6">';
          echo '<div class="thumbnail">';
          echo '<tr>';
          echo '<h4>';
          echo '<td>'.$row->fixture_type.'</td>'."</br></br>";
          echo '<td>'.$row->fixture_level.'</td>'."</br></br>";
          echo '<td>'.$row->fixture_comp.'</td>'."</br>";
          echo '</h4>';


          echo '<td>'.$row->fixture_text.'</td>'."</br>";

          echo '<td>'.$row->fixture_date.'</td>'."</br></br>";
          echo '</tr>';
          echo '</div>';
          echo '</div>';
         }
       }

      else echo "Not Array";
    }

    ?>

I am trying to create a fixture that will feature the team name and logo from team 1 followed by the fixture details then the team name and logo from team 2

I know its something like this for the JOIN but am not sure what to join on.

$this->db->select('*');
$this->db->from('fixture');
$this->db->join('team', '? = ?');

$query = $this->db->get();

Any help really appreciated

Sam · Accepted Answer · 2014-02-20 21:56:04Z

1

Try something like this

 $this->db->select('*');
$this->db->from('Team_Fixtures');
$this->db->join('team', 'Team_Fixtures.team_id = team.team_id');
$this->db->join('Fixture', 'Team_Fixtures.fixture_id= Fixture.fixture_id');
$query = $this->db->get();

answered Feb 20, 2014 at 21:56

Sam

2,7613 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sam Over a year ago

Team_Fixtures is the only table that holds the relationships so you have to put records in it if you want results.

Wicket W. Warwick Over a year ago

The team table is populated and the fixture table is populated but the team_fixtures table is empty so when I run this query $this->db->select('*'); $this->db->from('Team_Fixtures'); I get nothing back - Do I need to manually insert the ids for fixture and teams into it. This is where I am confused. This table only has two columns - team_team_id and fixture_fixture_id - should there not be a value for two teams if I am creating a fixture?? - the table was created automatically in MySql Workbench when I added a relationship between the team and fixture tables

Wicket W. Warwick Over a year ago

should the team_fixture table have team1_team_id and team2_team_id and fixture_fixture_id and if so are they all part of the primary key

Sam Over a year ago

the database design above is for many-many relationship. if you want a one to many you have to change the data design. in many-many Team_Fixtures holds foreign keys from the two tables related. tomjewett.com/dbdesign/dbdesign.php?page=manymany.php

Sam Over a year ago

put that as another question and describe what you want to retrieve.

Collectives™ on Stack Overflow

MySQL JOIN tables in Codeigniter

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related