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;`
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.