SQL - How to find anomalies (Part 2 - Introduction for beginners)

To be able to create more complex queries, you have to understand the database in the background. This lesson is very important if you don't really know how databases work or if you are not familiar with the Moodle database at all.
As I said earlier, we are going to find specific forums on our Moodle site, where our teachers, developers, course admins are most probably misusing the 'news' forum feature. Before you continue, please read the previous chapter here.

---

We used the following code previously and we are going to extend this code.

SQL

What happens if there is a forum on our anomaly report? We have to find it on the website and change it or delete it or do something else.
Right now we only know the courseID. So we have found the 6th forum in the 6th course called 'News forum'.

id	course	type	name
6	6	general	News forum

As I knew where to find it in real, this is the actual URL to the forum:
https://lengyelke.com/moodlelts/mod/forum/view.php​?id=78
What is the number at the very end of the URL? Why is it 78? Where is it in the database exactly? We will see soon. We have to have a look at another table called 'mdl_course_modules'.

id	course	module	instance
76	6	17	2
77	6	17	3
78	6	9	6
79	3	16	1

As you can see, there is one row where all the previously discovered numbers are in place:

• id = 78
• course = 6
• module = 9
• instance = 6

so we are close. What is module=9? Let's have a look at the 'mdl_modules' table.

id	name
7	feedback
8	folder
9	forum
10	glossary

These are the actual resources and activities we can use within a course and 9 means forum, so now we are ready to create a better query with an actual URL.
Please note: on your Moodle site the modules might be different or they have different id numbers, so discover your database first. But the code I am going to provide is going to work no matter what your module allocation is.
Let's have a final look at the tables and their relationship, before we move on.

Relations between tables

In this case instance=6 means forum.id=6, but only because module.id=9 which is a forum. If module was 10, then we should replace the mdl_forum table with mdl_glossary. I hope it makes sense.

SQL

SELECT CONCAT (
        'https://lengyelke.com/moodlelts/mod/forum/view.php?id=',
        cm.id
        ) AS link,
    f.course,
    f.type,
    f.name,
    f.intro
FROM prefix_forum AS f
JOIN prefix_course_modules AS cm
    ON cm.course = f.course
        AND cm.instance = f.id
JOIN prefix_modules AS m
    ON m.id = cm.module
        AND m.name = 'forum'
WHERE (
        f.name LIKE '%News%'
        OR f.name LIKE '%Announcement%'
        )
    AND f.type <> 'news'   

• 9: we work with forums basically
• 10: we have to find the course module id (=78), to be able to provide the link
• 11: they are linked by the same course id
• 12: the instance number = forum id but only if the module is a forum
• 13: so check what kind of module it is
• 14: link between the 2 tables
• 15: make sure it is a forum activity
• 16+: find faked news forums
• 1-8: what we would like to see on the report

Please spend some time to fully understand the code, take your time.

Click here to see how it works on my dummy Moodle site. Don't forget to log in as a guest. Come back for more tutorials. Enjoy!