The following SQL scripts might be helpful to find different anomalies on your Moodle site.
Modules and their ID number in your database
Before you adopt one of the following codes, you have to check your module IDs in your database as the codes are based on our modules table and it can be different in your database.
Start with the following query:
select * from mdl_modules
and save it somewhere.
Our most popular modules are:
Id | Name |
---|---|
11 | lesson |
12 | page |
13 | quiz |
15 | scorm |
17 | url |
21 | journal |
28 | book |
37 | hsuforum |
44 | lti |
55 | assign |
79 | webexactivity |
84 | hvp |
Your database might be different, so you have to use your ID numbers in the following queries. The code looks similar to this one:
... AND cm.module = 11 -- 11 is our module ID for lessons, you have to use your own ID
Let start with Lessons
Re-take a lesson
If a lesson contains at least one quiz page, it is important to think about the 'Re-takes allowed' setting under the Grade section. If enabled, students can attempt the lesson more than once, otherwise they get an error message:
You are not allowed to retake this lesson.
The following report helps you to find these lessons with quiz pages but disabled re-take setting:
SELECT DISTINCT c.id AS cid, c.shortname, cm.id AS cmid, l.id AS lid, l.name, l.retake FROM mdl_lesson_pages AS lp JOIN mdl_lesson AS l ON lp.lessonid = l.id JOIN mdl_course AS c ON l.course = c.id JOIN mdl_course_modules AS cm ON cm.course = c.id AND cm.instance = l.id AND cm.module = 11 -- don't forget to change it, if your modules table is different WHERE c.visible = 1 -- only visible courses AND cm.visible = 1 -- only visible activities AND l.retake = 0 -- retakes not allowed AND lp.qtype <> 20 -- branch / content, not quiz AND lp.qtype <> 21 -- end of branch, not quiz AND lp.qtype <> 30 -- cluster, not quiz AND lp.qtype <> 31 -- end of cluster, not quiz
The other qtype codes are:
page / quiz type | qtype code |
---|---|
LESSON_PAGE_SHORTANSWER | 1 |
LESSON_PAGE_TRUEFALSE | 2 |
LESSON_PAGE_MULTICHOICE | 3 |
LESSON_PAGE_MATCHING | 5 |
LESSON_PAGE_NUMERICAL | 8 |
Find graded lessons
The next script helps you to find graded lessons, in our VLE we don't want them to be graded.
SELECT cm.id AS cmid, c.id AS cid, l.name AS lname, l.id AS lid FROM mdl_lesson AS l JOIN mdl_course AS c ON l.course = c.id JOIN mdl_course_modules AS cm ON cm.course = c.id AND cm.instance = l.id AND cm.module = 11 -- don't forget to change this number based on your modules table WHERE l.grade > 0
I hope you will find these reports helpful.
Enjoy!
Frank