0

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;`
GMB
  • 216,147
  • 25
  • 84
  • 135
bjorn
  • 129
  • 1
  • 11

1 Answers1

0

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.

GMB
  • 216,147
  • 25
  • 84
  • 135