There are two tabs: category and news. You need to make a selection to show 5 news each category. This list shows all the news.

SELECT category.category, news.title FROM category, news WHERE news.category_id=category.id

But you need: to the site displayed in this order. example:

  1. Category1

  2. news 1

  3. news 2

  4. news 3

  5. Category 2

  6. news 1

  7. news 2
  8. news 3 and so on ...

3 answers 3

 set @rank = 0; set @cursec = -1; select * from ( select s.name sname, e.name ename, (IF (@cursec != s.id,(@rank := 1),(@rank := @rank + 1))) RANK, @cursec := s.id CURSEC from sect s left join elem e on e.sec = s.id order by s.id,e.id ) T where rank <= 2;

    One request will fail. The first query, select categories, remember them in the list. Then go through the cycle on this list and select the news of this category with the addition of LIMIT 5 to the query. This will limit the selection to 5 news items. By what principle you will sort them - your business. Put it all on the page.

    Select all categories and news in the correct order, wrap the result in a subquery, add a counter to it and number the news separately for each category, and then just leave the ones you need.

    Example for LIMIT 2 (change at your discretion to 5 or whatever):

     SELECT t.*, @newsNum := IF(t.id != @prevCatId, 1, @newsNum + 1) n, @prevCatId := t.id FROM ( SELECT c.id, c.category, n.title FROM _category c INNER JOIN _news n ON n.category_id = c.id ORDER BY c.id, n.id ) t INNER JOIN (SELECT @prevCatId := 0, @newsNum := 0) counters HAVING n < 3;

    Result (hidden fields):

     1 sport news 1.1 1 sport news 1.2 2 cinema news 2.1 2 cinema news 2.2 3 music news 3.1 3 music news 3.2

    For debugging I used such temporary tables:

     CREATE TEMPORARY TABLE _category(id int, category char(8)) SELECT 1 id, 'sport' category UNION SELECT 2 id, 'cinema' category UNION SELECT 3 id, 'music' category ; CREATE TEMPORARY TABLE _news(id int, category_id int, title char(16)) SELECT 1 id, 1 category_id, 'news 1.1' title UNION SELECT 2 id, 1 category_id, 'news 1.2' title UNION SELECT 3 id, 1 category_id, 'news 1.3' title UNION SELECT 4 id, 2 category_id, 'news 2.1' title UNION SELECT 5 id, 2 category_id, 'news 2.2' title UNION SELECT 6 id, 2 category_id, 'news 2.3' title UNION SELECT 7 id, 3 category_id, 'news 3.1' title UNION SELECT 8 id, 3 category_id, 'news 3.2' title UNION SELECT 9 id, 3 category_id, 'news 3.3' title ;