What SQL statement will display the students enrolled in a given course? It would be enough to see the idnumbers or usernames of all students enrolled in a course identified by its idnumber.
In our case the enrolment data ultimately are coming from an external db, but I am asking about the local data which Moodle creates in its own MySQL db.
Thanks!
This is what I use on the command line, but it should work in sqlYOG, phpmyadmin or kmysqladmin...
SELECT c.fullname, u.firstname, u.lastname, u.email, u.id FROM mdl_course c LEFT OUTER JOIN mdl_context cx ON c.id = cx.instanceid LEFT OUTER JOIN mdl_role_assignments ra ON cx.id = ra.contextid AND ra.roleid = '5' LEFT OUTER JOIN mdl_user u ON ra.userid = u.id Where cx.contextlevel = '50';
that will give you what students are enrolled in which courses
SELECT c.fullname, u.firstname, u.lastname, u.email, u.id FROM mdl_course c LEFT OUTER JOIN mdl_context cx ON c.id = cx.instanceid LEFT OUTER JOIN mdl_role_assignments ra ON cx.id = ra.contextid AND ra.roleid = '5' LEFT OUTER JOIN mdl_user u ON ra.userid = u.id Where cx.contextlevel = '50';
that will give you what students are enrolled in which courses
You could also insert an additional c.id (course id #) to show the course id # in the select statement.
ie:
SELECT c.id, c.fullname, u.firstname, u.lastname, u.email, u.id FROM mdl_course c LEFT OUTER JOIN mdl_context cx ON c.id = cx.instanceid LEFT OUTER JOIN mdl_role_assignments ra ON cx.id = ra.contextid AND ra.roleid = '5' LEFT OUTER JOIN mdl_user u ON ra.userid = u.id Where cx.contextlevel = '50';
ie:
SELECT c.id, c.fullname, u.firstname, u.lastname, u.email, u.id FROM mdl_course c LEFT OUTER JOIN mdl_context cx ON c.id = cx.instanceid LEFT OUTER JOIN mdl_role_assignments ra ON cx.id = ra.contextid AND ra.roleid = '5' LEFT OUTER JOIN mdl_user u ON ra.userid = u.id Where cx.contextlevel = '50';
Thanks!
Is there some documentation somewhere about how "context" works, what the values are for roles, etc.? For example, what number is the value for instructor?
Is there some documentation somewhere about how "context" works, what the values are for roles, etc.? For example, what number is the value for instructor?
Hi to you both
Thanks to GP so much for that query!
I was able to ferret out the context ids from the URL in the browser when manually enrolling users. My first course is context 14 and my second is 26, for whatever reason.
I'm not getting anywhere with the enrol ext db plugin. It just doesn't work, or log useful errors, and it's holding up my project and frustrating me.
So it looks like I'll be manually updating the mdl_roles_assignment table some way, which does work.
eg: mysql> insert into mdl_role_assignments (roleid,contextid,userid,hidden,timestart,timeend, timemodified,modifierid,enrol,sortorder) VALUES (5,14,10,0,NOW(),0,NOW(), 2, 'manual',0);
seemingly enrolled a user in a class which the enrol cron seems to have never done. Pardon my SQL.
Hope that helps you some,
adric
I am beginning to worry about my sanity in regards to external DB enrolment, as I had it working a month ago on another system but now find that it is not working as I prepare for actual deployment! Is something wrong with the latest 1.9.3 weekly build?
The users are authenticated by LDAP, and I know that works because I can log in as a student without any problem. The enrollment is determined through a SQL Server table function, and I know that it returns the correct result set, both because I test it on the SQL Server external db, but more importantly because I can see that the students were indeed enrolled, after I ran /enrol/database/enrol_database_sync.php. (Attached is XML-formatted output from MySQL when executing the very useful SQL earlier in this thread. You can see that various students (test accounts) are enrolled in a course "Robot Test AZ".)
Yet if I log in as one of these students there are no courses listed as "My Courses". The startdate for the course is the 10th of November, 2008, and the course is set to visible. The Front Page settings say to list courses after log in. (If I set the course to enrollable then a student can manually self-enrol, but that is not what I want.)
I already tried turning on error logging inside the enrol/database/enrol.php file, and it indicates that students are found to be enrolled, as I can see from the MySQL data anyway. It is just the per-student login process that refuses to recognize the student's enrolment in the course; the general syncronization code is working. Why might this be? They both use the same table function to retrieve data from the external db source, no?
Please tell me what I should be looking at. Is there some setting that needs to be turned on in order for external database enrolments to work? Everything seems to be set up correctly in the mdl_config table.
Thanks for any help or suggestions anyone can offer.
Alan
The users are authenticated by LDAP, and I know that works because I can log in as a student without any problem. The enrollment is determined through a SQL Server table function, and I know that it returns the correct result set, both because I test it on the SQL Server external db, but more importantly because I can see that the students were indeed enrolled, after I ran /enrol/database/enrol_database_sync.php. (Attached is XML-formatted output from MySQL when executing the very useful SQL earlier in this thread. You can see that various students (test accounts) are enrolled in a course "Robot Test AZ".)
Yet if I log in as one of these students there are no courses listed as "My Courses". The startdate for the course is the 10th of November, 2008, and the course is set to visible. The Front Page settings say to list courses after log in. (If I set the course to enrollable then a student can manually self-enrol, but that is not what I want.)
I already tried turning on error logging inside the enrol/database/enrol.php file, and it indicates that students are found to be enrolled, as I can see from the MySQL data anyway. It is just the per-student login process that refuses to recognize the student's enrolment in the course; the general syncronization code is working. Why might this be? They both use the same table function to retrieve data from the external db source, no?
Please tell me what I should be looking at. Is there some setting that needs to be turned on in order for external database enrolments to work? Everything seems to be set up correctly in the mdl_config table.
Thanks for any help or suggestions anyone can offer.
Alan
Well, Development:Database_schema_introduction#The_roles_and_capabilites_system wsa the documentation I started of the database schema. And the link in the heading takes you to the roles documentation.
Thanks. I now think that there was some strange interaction between the "visibility" setting for the template course used to dynamically create the actual courses, coupled with "cockpit error" as I manually tried various settings. Sorry for the unnecessary drama I introduced to the thread...
Thanks Matthew this query saved me a ton of time, really great work!
Hi matthew Burford,
Thanks for Answer.
This query worked for me but it was not worked in web-service (rest). of moodle. Why?
Thanks,
Swanand (Sonya)