title: Codeigniter Insert into Many-to-Many Joining Table/Pivot Table
body: <p>I apologise if this has been answered previously (and would greatly appreciate it if someone could point me to it), but how would I go about inserting into a many-to-many join table? </p>
  
  <p>Essentially I have a contest based on sports events, and hence have a join table to match the contest_id to the relevant sports_events_id . If I were to create a new contest, how would I also insert into the join table database simultaneously? </p>
  
  <p>Or, would I have to create the contest first to get the contest_id, and then (and only then) insert into the joining table all the relevant sports_events_id?</p>
  
  <p>I appreciate any assistance and advice you could provide.</p>
  
  <p>Many thanks.</p>
  
  <p><strong>Solution</strong></p>
  
  <p>Ok so I've now got the following solution to insert into the joining table</p>
  
  <p><strong>Controller</strong></p>
  
  <pre><code>function add()
      {
          $this-&gt;form_validation-&gt;set_rules('contest_name', 'Contest Name', 'required|trim|xss_clean');
  
          if ($this-&gt;form_validation-&gt;run() == FALSE) {
  
              $data['view_file'] = 'add_contest';
              $this-&gt;load-&gt;module('template');
              $this-&gt;template-&gt;cmslayout($data);
  
          } else {
  
              $data1 = array(
                  'contest_name' =&gt; $this-&gt;input-&gt;post('contest_name'),
              );
  
              $data2 = $this-&gt;input-&gt;post('game');
  
              if ($this-&gt;_transactions_new_contest($data1, $data2)) {
                  return $query;
              }
              redirect('/contests/');
          }
      }
  
  function _transactions_new_contest($data1, $data2) {
      $this-&gt;load-&gt;model('mdl_contests');
      $this-&gt;mdl_contests-&gt;_transactions_new_contest($data1, $data2);
  }
  </code></pre>
  
  <p><strong>Model</strong></p>
  
  <pre><code>function _transactions_new_contest($data1, $data2){
      $this-&gt;db-&gt;trans_start();
      $this-&gt;db-&gt;insert('contests', $data1);
      $contest_id = $this-&gt;db-&gt;query('SELECT contests.id FROM contests ORDER BY contests.id DESC limit 1');
      foreach ($contest_id-&gt;result() as $row) {
          $contest_result_id = $row-&gt;id;
          $this-&gt;db-&gt;query('INSERT INTO contests_has_sports_events (contests_id, sports_events_id) VALUES (' . $contest_result_id . ', ' . $data2 . ')');
      }
      $this-&gt;db-&gt;trans_complete();
  }
  </code></pre>
  
  <p>Now the problem is, how do I now loop the $data2 if I have multiple sports_events_id or post('game')?</p>
  
  <p>Once again, if you could point me in the right direction it would be greatly appreciated.</p>