title: mysql one to many return, dealing with pagination
body: <p>So I have the following (abbreviated) tables:</p>
  
  <pre><code>resource
  id | name
  
  resource_category
  resource_id | category_id
  
  category
  id | name
  </code></pre>
  
  <p>When I want to get the resources and all the assigned categories I use
  the following sql:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT resource.id. resource.name, category.id, category.name FROM resource
  LEFT JOIN resource_category ON resource_category.resource_id = resource.id
  LEFT JOIN category ON category.id = resource_category.category_id
  </code></pre>
  
  <p>This will get the following resourlces</p>
  
  <pre><code>resource.name1 | 1 | category1
  resource.name1 | 2 | category2
  resource.name1 | 5 | category5
  resource.name2 | 5 | category5
  resource.name2 | 6 | category6
  resource.name2 | 1 | category1
  </code></pre>
  
  <p>I was then looping through and compressing the results into arrays</p>
  
  <pre class="lang-php prettyprint-override"><code>foreach( $results as $result ) {
      $resources[$result['id']]['resource'] = $result;
      $resources[$result['id']]['categories'][$result['category_id']] =
      $result['category_name'];
  }
  </code></pre>
  
  <p>Then I'd have a resource and all of it's categories.</p>
  
  <p>The problem I'm running into is that when I start doing pagination and
  use LIMIT, the results are giving 10 results, but since there's
  multiple results for a single resource, when I compress it I get 2
  actual resources instead of 10. Any suggestions?</p>
  
  <p>I was trying to stay away from the below code:</p>
  
  <pre class="lang-php prettyprint-override"><code>foreach( $results as $result ) {
        $resources[$result['id']]['resource'] = $result;
        $resources[$result['id']]['categories'] =
        $this-&gt;resource_model-&gt;get_categories( $result['id'] );
  }
  </code></pre>