Synching Most-Recent Articles & Random Article
When a user chooses a "Section" (e.g. Finance) from the top menu, he/she is taken to a "Section Landing Page", which includes a listing of related "Sub-Sections" (Economy, Markets, Investing).
Under each "Sub-Section", there are two areas:
On the right will be a listing of the "4 Most Recent Articles" displayed as hyperlinks.
On the left, will be a "Featured Article" which will have a Thumbnail and Short Summary (designed to highlight the article).
Now on to my MySQL questions...
First, I need to retrieve the "5 Most Recent Articles". (Easy enough)
SELECT a.heading, s.name, a_s.primary_section, a.published_on
FROM article AS a
INNER JOIN article_section AS a_s
ON a.id = a_s.article_id
INNER JOIN SECTION AS s
ON s.id = a_s.section_id
WHERE s.name = 'finance'
AND published_on < '2012-01-15 00:00:00'
ORDER BY a.published_on DESC
Next, I want to randomly grab one of those records and use it for the "Featured Article" on the left.
Then, the remaining 4 Articles will be used in the right area.
(**The KEY is that the same Articles should never be displayed in both areas.)
Honestly, I'm not sure if this is a MySQL problem, or if I should be handling this with PHP, but I'll see what you guys think...