I have a database that includes the following tables:
- User
- Category
- Article
Inside our website we have a section called "editors pick" that has 5 of the best articles chosen by editors on that area.
Editors must set "is_recommended = yes" and also "recommended_location" which can be either 1,2,3,4, or 5; accordingly they will be placed on one of those 1-5 placements on the website.
Articles also have a "start_date" meaning the author can write an article assign it as is_recommended = yes and recommended_location = 3 and then set it for tomorrow 9pm. Therefore the article will only appear tomorrow and when it does it should sit on the 3 box of the editors pick.
Sometimes we may have an article such as below:
- ID: 123
- is_recommended: yes
- recommended_location = 3
- start_date = 06-05-2016 09:00:00 (let's say this is yesterday)
Which is currently holding the #3 spot.
I have another article:
- ID: 456
- is_recommended: yes
- recommended_location = 3
- start_date = 07-05-2016 09:00:00 (this is today and today it is already 11am)
However my query still continues to show ID: 123; while I want it to show the one in slot #3 that is the newest (meaning 456)
Can someone tell me what I am doing wrong in my query below, how can I assure that for each slot the newest item is selected?
This is the query:
select *
from (
select article.*, user.username, category.title as ctitle, user.firstname, user.lastname, category.slug as cslug, category.category_id as pid
from article
left join user on article.created_by = user.id
left join category on category.id = article.category_id
where article.status='active'
AND is_recommended='yes'
AND article.start_date<='".date('Y-m-d H:i:s')."'
AND recommended_location in (1,2,3,4,5)
order by start_date desc
) as x
group by recommended_location
limit 5