mysql一对多返回，处理分页

So I have the following (abbreviated) tables:

resource
id | name

resource_category
resource_id | category_id

category
id | name

When I want to get the resources and all the assigned categories I use the following sql:

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

This will get the following resourlces

resource.name1 | 1 | category1
resource.name1 | 2 | category2
resource.name1 | 5 | category5
resource.name2 | 5 | category5
resource.name2 | 6 | category6
resource.name2 | 1 | category1

I was then looping through and compressing the results into arrays

foreach( $results as $result ) {
    $resources[$result['id']]['resource'] = $result;
    $resources[$result['id']]['categories'][$result['category_id']] =
    $result['category_name'];
}

Then I'd have a resource and all of it's categories.

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?

I was trying to stay away from the below code:

foreach( $results as $result ) {
      $resources[$result['id']]['resource'] = $result;
      $resources[$result['id']]['categories'] =
      $this->resource_model->get_categories( $result['id'] );
}

The answer to your question is that you need a stable sort. Explicitly add an order by:

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
order by resource.id, category.id;

If you are just putting these in arrays, try group_concat():

SELECT resource.id. resource.name,
       group_concat(category.id) as cids,
       group_concat(category.name) as cnames
FROM resource
LEFT JOIN resource_category ON resource_category.resource_id = resource.id
LEFT JOIN category ON category.id = resource_category.category_id
group by resource.id;