In a category list, I'd like to be able to display the list of articles dynamically. The website I'm developing allows a user to "submit" an article once they have read it. When they hit the submit button, the ID of that article is stored in the database in a table called "completed_quests." What I need to do is have Joomla check to see if the article ID exists in both the "content" and the "completed_quests" tables. If the article ID exists in both tables, then the article should not be displayed in the category list as that article or "quest" has already been submitted. If the article ID exists in the "content" table, but NOT in the "completed_quests" table, then the article SHOULD be displayed in the category list as it has note yet been submitted.
I'm wondering if there is a specific core Joomla file I should override to alter the category list output, or if I should develop a custom module to create this dynamic list. Any guidance would be much appreciated. If you have any other thoughts about how to display this dynamic list, I'm all ears.
edit: I've started by developing a custom module within Joomla, at least for testing. The below code is not working as expected. When I take out the following line "WHERE arp2i_completed_quests.id IS NULL" it displays a list of articles that exist in both tables. But what I need it to do is display the rows that exist in the content table but NOT in the completed_quests table. When I add the WHERE , it displays the text "ID:" and "TABLE:" for each row that exists, but the actual id and title from that row is not echo'ed to the screen. Please help.
Working code (see comments).
<?php
$query = "SELECT * FROM arp2i_completed_quests RIGHT JOIN arp2i_content ON arp2i_content.id=arp2i_completed_quests.id WHERE arp2i_completed_quests.id IS NULL LIMIT 0, 30 "; // prepare query
$db = &JFactory::getDBO(); // get database object
$db->setQuery($query); // apply query
$articles = $db->loadObjectList(); // execute query, return result list
foreach($articles as $article){ // loop through articles
echo 'ID:' . $article->id . ' Title: ' . $article->title . '<br />';
}
?>
