SQL query to get all posts from a table in the db but max 5 entrys from a specific user?

I´m building a php-blog system and want to display all posts but max five from each user on the start page. I thinking of do this with a query in the database, but I´m lost on how to do that. The count() function I guess will come in handy, but can somebody help me

This is my function today, and I just whant to improve it to get max five posts from each user

protected function getAllPostsDB() {     $sql = "SELECT recipes.Recipe_ID, recipes.Title, recipes.Short_description, recipes.Step_by_step,      recipes.create_date, recipes.last_mod_date, recipes.Portions, recipes.imgPath, users.Username     FROM recipes      JOIN users         ON recipes.User_ID = users.User_ID     ORDER BY recipes.create_date DESC";     $stmt = $this->connect()->query($sql);     /* fetch all is already set to associative array*/     $result = $stmt->fetchAll();     return $result;` 
Add Comment
1 Answer(s)

If you are running MySQL 8.0, just use window functions:

SELECT r.Recipe_ID, r.Title, r.Short_description, r.Step_by_step,      r.create_date, r.last_mod_date, r.Portions, r.imgPath, u.Username FROM (     SELECT r.*, ROW_NUMBER() OVER(PARTITION BY User_ID ORDER BY create_date DESC) rn     FROM recipes r ) r INNER JOIN users ON r.User_ID = u.User_ID WHERE r.rn <= 5 ORDER BY r.create_date DESC 

This gives the last five recipes per user, as designated by column create_date. You can change the ORDER BY clause of ROW_NUMBER() to some other column or set of columns if you want another sort rule.

Answered on September 1, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.