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.
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.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';
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?
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,
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.
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?