mysql get lastess articles from table with every category

Question

I working on project tintuc at http://www.xaluan.com

i have two table cuted to simple like this

table articles
 ID - Title - Des - CatID
table category
 CatID - catTitle 

normally i will run a loop over category table to received CatID and catTile then run again loop on articles table to have 4 latest articles belong to that CatID

result on website like this

catTitle 1 
 - lastes article belong to catID 1
 - second last artice belong to catID 1
catTitle 2
 - lastess article belong to catID 2
 - second last article belong to catID 2

I think the loop over category table then loop many time on article table for each catId is not effect way.

Please help with most effect mysql query to have same result.

thank a ton.

Answer 1

You can't get the result in the format you mentioned, however, you can run this query and loop over the result to format the data to look like hwa tyou have shown above.

SELECT c.catTitle, c.CatID, a.Title, a.ID , a.Des
FROM category c, artices a
WHERE c.CatID  = a.CatID 
ORDER BY c.CatID ASC, a.ID DESC

EDIT:

If you want only top 4, use this query

SELECT c.catTitle, c.CatID, a.Title, a.ID , a.Des
FROM category c, (SELECT ID, Title, Des, CatID
FROM
(
   SELECT ID, Title, Des, CatID,
      @num := if(@CatID = `CatID`, @num + 1, 1) AS row_number,
      @CatID := `CatID` AS dummy
  FROM articles
  ORDER BY CatID, ID DESC
) AS x WHERE x.row_number <= 4) a
WHERE c.CatID  = a.CatID
ORDER BY c.CatID ASC, a.ID DESC

demo

Answer 2

If ID is what you're sorting by (I don't see an article date to order by), the query can be written as;

SELECT a.* FROM articles a
WHERE (
   SELECT COUNT(b.id) FROM articles b WHERE a.id < b.id AND a.CatID = b.CatID
) < 4;

It shows all articles where there exist less than 4 newer articles in the same category.

An SQLfiddle to test with.