Appart from the mindful and meaningful discussion about Dougiamas' new haircut, I would like to introduce here a very substancial subject which is driving me mad! (and I need to have it done by the end of January).
I am a poor ignorant telecommunication engineer reclycled into learning technologist (sound great, isn't it?
), but I am not a developer and have a limited knowledge about mySQL:I am in charge of the college's VLE, with a total number of 610 courses and 4000 users. Theses courses are divided into 7 major categories (each category corresponds to each one of the college's schools), which are divided for their into different subcategories according to the academic curriculum.
My manager wants to know, for each one of the 7 schools, the number of students per course, that is, the numer of students per each one of the 610 courses. As for the Moodle version, I have 1.8.2.
I have tackled the query in two different ways:
1) I have tried to launch the query in Access 2002, and for that, I have created an ODBC with the Moodle server database. Through the ODBC, I link the Access tables with the tables in the server. Then, I launch the following query in Access:
SELECT mdl_course.fullname, mdl_course.shortname, mdl_course_display.course, mdl_course.category, mdl_user.firstname, mdl_user.lastname, mdl_user.username, mdl_user.id, mdl_course_display.userid INTO users_course
FROM (mdl_course_display INNER JOIN mdl_user ON mdl_course_display.userid = mdl_user.id) INNER JOIN mdl_course ON mdl_course_display.course = mdl_course.id
GROUP BY mdl_course.fullname, mdl_course.shortname, mdl_course_display.course, mdl_course.category, mdl_user.firstname, mdl_user.lastname, mdl_user.username, mdl_user.id, mdl_course_display.userid;
Because, unless I am wrong, the only way I could find to relate the users id with the courses id in this version of Moodle (I can't find the table mdl_user_students from the previous version of Moodle).
However, what I get in this way is a table where courses are listed in rows, being repeted per users ocurrences (For example: imagine I have a course called "How to use Moodle" with 10 users enrolled, then, I would get this course appearing in 10 raws (showing me the name of the 10 users in each raw).
This solution is quite tedious, as I still have to count the ocurrences manualy for each one of the 610 courses.
b) Launching the query in phpMyadmin.
I think this is the best method.
This query has appearded a lot in the forums, and I got the following code from one of the discussions:
SELECT mdl_course.fullname, mdl_usr.firstname, mdl_usr.lastname
FROM mdl_course
INNER JOIN mdl_context ON mdl_context.id = mdl_context.instanceid AND mdl_context.contextlevel = '50'
INNER JOIN mdl_role_assignments ON mdl_context.id = mdl_role_assignments.contextid
INNER JOIN mdl_role ON mdl_role_assignments.roleid = mdl_role.id
INNER JOIN mdl_user ON mdl_role_assignments.userid = mdl_usr.id
WHERE mdl_role.name = 'Student'
ORDER BY mdl_course.fullname, mdl_usr.firstname
Although the developer who posted in the discussion forum assured that it did work, I have checked the instruction in detail and makes no sense to me, as the sentence:
mdl_context.id = mdl_context.instanceid, whit the data that I have in my table mdl_context, it is impossible to match.
I would really appreciate any suggestion or code from you, wise and smart Moodle developers!!!!
Thank you very much!