## General developer forum

### Databases: How can I find which users are enrolled to which course- database query

This discussion has been locked because a year has elapsed since the last post. Please start a new discussion topic.
Databases: How can I find which users are enrolled to which course- database query

Hi ,

I tried to have a a look at the database but unable to find a solution " For a given course id which all users have enrolled".

I understand it is somewhat related to table mdl_role_assignments but in that we have content not course id.

Thanks
Saurabh

Average of ratings: -
Re: Databases: How can I find which users are enrolled to which course- database query

Hi Saurabh,

use this method.Available in Moodle 1.9.1 +

get_course_students($CourseID); Average of ratings: - Re: Databases: How can I find which users are enrolled to which course- database query get_course_students() is in lib/deprecatedlib.php What should now be used in place of get_course_students()? Average of ratings: - Re: Databases: How can I find which users are enrolled to which course- database query Thanks for the reply. I want to know which tables have that info , How can i get the info using sql query. Actually i am implementing a reminder module via database triggers so want to get the info via tables. Thanks & Regards Saurabh Average of ratings: - Re: Databases: How can I find which users are enrolled to which course- database query I think role, role_assignments and context are what you're looking for. A course will have a context. Users are assigned roles (such as student) within that context through role_assignments. Average of ratings: - Re: Databases: How can I find which users are enrolled to which course- database query thanks for the info , I looked at both the tables but not able to get which context for which course, How can i relate course to context. Thanks Saurabh Average of ratings: - Re: Databases: How can I find which users are enrolled to which course- database query Not sure how to query for it, but it is stored in global$COURSE in \$COURSE->context So that's a place to start atleast. If you find out where all this is in the tables, please let me know!

Average of ratings: -
Re: Databases: How can I find which users are enrolled to which course- database query

this is the query you need(if you just want the student use the roleid=5):

SELECT c.id AS courseid, c.fullname, u.firstname, u.lastname
FROM mdl_role_assignments ra, mdl_user u, mdl_course c, mdl_context cxt
WHERE ra.userid = u.id
AND ra.contextid = cxt.id
AND cxt.contextlevel =50
AND cxt.instanceid = c.id
AND roleid =5

Average of ratings: Useful (1)
Re: Databases: How can I find which users are enrolled to which course- database query

Great, thanks!

Average of ratings: -
Re: Databases: How can I find which users are enrolled to which course- database query

Thanks Zahra This is what I was looking for exactly.

Average of ratings: -
Re: Databases: How can I find which users are enrolled to which course- database query

Zahra, you're my hero!

Average of ratings: -
Re: Databases: How can I find which users are enrolled to which course- database query

Brilliant! Works so much better than my SQL; mine included classes the student had been in before, but are not currently in.

Average of ratings: -
Re: Databases: How can I find which users are enrolled to which course- database query

Your query is giving results who are locally assigned (suppose course->assign roles)...
but when a user assigned globally meaning assign roles in system (Users->Permission->Assign system roles) then how to get those users by query?. Can you please give me the hints/or that query?

Average of ratings: -
Re: Databases: How can I find which users are enrolled to which course- database query