SQL statement

So, the whole project boils down to reading parameter options, constructing the SQL query, randomly choosing an article out of the returned record set and printing out the link. That’s it.

Onto the SQL statement, we want to select the id, the catid, the sectionid and the title from the jos_content table where the section id falls within the selected ones, excluding (most likely) the items that are on the jos_content_frontpage table while limiting the resulting rows and ordering them by date, starting from the latest. OK, it should look similar to the following query.

SELECT id, catid, sectionid, title
FROM jos_content
WHERE state=1
AND (sectionid = 1 OR sectionid = 4)
AND id NOT IN (SELECT content_id FROM jos_content_frontpage) 
ORDER BY created ASC
LIMIT 0, 5

After understanding how the SQL statement should look like I bet you can start writing code. It doesn’t have to be too fancy nevertheless you need to think of something to get the SQL statement right for all possible cases. Give it a go alone first and see if you can make it.

Feel free to have a look at my suggestion.

It took me a while to understand the explode and implode functions, I suggest you look at the PHP manual.

Now, the truth is, this is a fairly simple query we are building here and we can get away without too much struggle. Imagine the task was more complicated or read the code of one of the standard modules, the mod_latestnews for example, it looks a bit terrifying, isn’t it?. The thing is, the styling of the code or the intelligence of the SQL statement can improve readability a lot (or just make you feel smarter).

Here follows a better version worth reading.

To the “what the hell are these question marks” people out there: These are “if” statements in a glorious but rather cryptic C-like format. The syntax is:

<condition> ? <expr if cond is true> : <expr if cond is false> ;

Hope it makes sense.