php - Limit 10 records from posts for each category -

this question has answer here:

• using limit within group n results per group? 14 answers

i have 2 table categories , posts, don't want records each category. want limited rows each category.

categories table below :-

1. id
2. name
3. slug

posts table below :-

1. id [ pk ]
2. title
3. slug
4. content
5. category [key - foreign key]
6. publish_date

what trying achieve , want 10 records posts each category.

what doing @ moment dangerous, runs lots of query, want minimize 1 query.

<?php      $fetchcat = $mysqli->query("select * categories");     while($row = $fetchcat->fetch_assoc()) {         $fetchpost = $mysqli->query("select id, title, slug posts category=".$mysqli->real_escape_string($row['id'])." limit 10");         // processing code.     } ?> 

can have "inner join" query, can reduce query 1-2 query , me same results above 1 ?

i want have 10 articles each category fetched. in future, may have 40-45 category, , each category , on average, may have 80-90 posts. while fetching posts 40-45 category above method, can take application on coaster ride. need feasible method, can limit posts record each 40-45 category.

this not simple inner join, fetching posts, limiting inner join records display each parent table.

i found solution in 2 query. there little improvement.

1st query, ran category , stored them in array.

$cat_array = array(); $fetchcat = $mysqli->query("select * categories"); while($rowcat = $fetchcat->fetch_assoc()) {    // category processing.... } 

second query , ran against post using group_concat , substring_index 10 records of each category.

$post_array = array(); $fetchpost = $mysqli->query("select category,               substring_index(group_concat(id), ',', 10) post_id,               substring_index(group_concat(title), ',', 10) post_title,               substring_index(group_concat(slug), ',', 10) post_slug posts               group category;");  while($rowpost = $fetchpost->fetch_assoc()) {     $post_array[ $rowpost['category'] ] [id] = $rowpost['post_id'];     $post_array[ $rowpost['category'] ] [title] = $rowpost['post_title'];     $post_array[ $rowpost['category'] ] [slug] = $rowpost['post_slug']; } 

2 query , required data [ categories table data, 10 posts table data each category ]

i had explode post_id , post_title, post_slug , use in application.

now, to list of title , slug category, simple, eg, category id "1" had :-

$post_array[1][id]  // returns post_id associated category 1. 

a great thank @billynoah, pointing me in "group wise" direction, "asconfused" going through query , letting me know, there command analyze table posts too.

thanks