The purpose of this blog is more or less a placeholder. I've written up my SQL:
SELECT `mdl_course`.`fullname` as 'Course Name'
, mdl_course.id as 'Course ID'
, sum(mdl_modules.id=1) as 'Assignment'
, sum(mdl_modules.id=2) as 'Chat'
, sum(mdl_modules.id=3) as 'Choice'
, sum(mdl_modules.id=4) as 'Data'
, sum(mdl_modules.id=5) as 'Forum'
, sum(mdl_modules.id=6) as 'Glossary'
, sum(mdl_modules.id=7) as 'HotPot'
, sum(mdl_modules.id=12) as 'Quiz'
, sum(mdl_modules.id=14) as 'SCORM'
, sum(mdl_modules.id=15) as 'Survey'
, sum(mdl_modules.id=16) as 'Wiki'
, sum(mdl_modules.id=17) as 'Workshop'
, sum(mdl_modules.id=22) as 'Feedback'
, sum(mdl_modules.id=25) as 'TurnitinTool'
FROM mdl_course
join mdl_course_modules on mdl_course.id = mdl_course_modules.course
join mdl_modules on mdl_course_modules.module = mdl_modules.id
group by 'Course name'
ORDER BY 'Course ID' ASC
and as you can see it's very pretty and sensible and should (theoretically) return the number of each activity type that features in courses on the site. What actually returns is:
| Course Name | Course ID | Assignment | Chat | Choice | Data | Forum | Glossary | HotPot | Quiz | SCORM | Survey | Wiki | Workshop | Feedback | TurnitinTool | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| GC VLE | 1 | 0 | 30 | 23 | 10 | 2897 | 247 | 158 | 989 | 299 | 11 | 122 | 39 | 1501 | 655 |
While this is surprisingly handy, it has to be said it is
- inaccurate - we have many assignments. But I think this is a result of the assignments now being a wholly separate situation from the other module types, and therefore not being registered on the module list. I need to look into this again.
- not what I need. I need each course to be listed and the activity types *per course* to be clearly identified.
I have absolutely no idea why it isn't working as it should, but I have a feeling my joins are causing the issue.
