title: Displaying data from two tables with many-to-many relation using PHP/CodeIgniter
body: <p>I am making simple connection between two table - first one called users has fields :</p>
  
  <blockquote>
    <p>|| id || name ||</p>
  </blockquote>
  
  <p>and the second table called groups has the same fields:</p>
  
  <blockquote>
    <p>|| id || name ||</p>
  </blockquote>
  
  <p>The relations b/w them is many to mane because one user can be in many groups(of interest) and a group will contain more than one user. So I have a third table user_group with fields:</p>
  
  <blockquote>
    <p>|| user_id || group_id ||</p>
  </blockquote>
  
  <p>Where the both are foregin keys one for users and one for groups.</p>
  
  <p>The goal is to make a list with all the groups and the people participating in each one.
  For this I made my query:</p>
  
  <pre><code>$q = $this-&gt;db-&gt;query('SELECT u.name  FROM users u 
  JOIN user_group ug ON u.id = ug.user_id 
  JOIN groups g ON g.id = ug.group_id WHERE ug.group_id = "4" ');
  </code></pre>
  
  <p>then modified it using active records:</p>
  
  <pre><code>$this-&gt;db-&gt;select('users.name');
  $this-&gt;db-&gt;from('users');
  $this-&gt;db-&gt;join('user_group', 'user_group.user_id = users.id');
  $this-&gt;db-&gt;join('groups', 'groups.id = user_group.group_id');
  $this-&gt;db-&gt;where('user_group.group_id', 3);
  $q = $this-&gt;db-&gt;get();
  </code></pre>
  
  <p>And this way I can take the users for any group by given the 'id' from the group table. But what I can't figure out is how to make it display both - the name of the group along with the users participating. When I create and delete from the tables the id's become very unorder I may have 20 groups and some group may have id = 53 so jsut looping from 1 to number_of_groups is not good enough. What's the way to do this?</p>
  
  <p>Thanks
  Leron</p>