## General developer forum

### Databases: How can I get enrolled users for a course using SQL

This discussion has been locked because a year has elapsed since the last post. Please start a new discussion topic.
Databases: How can I get enrolled users for a course using SQL

Hi, I hope this is the right place to ask this question, how can I get the enrolled users for a course.

I took a look to the database tables but I wasn´t able to figure it out. I would like to get this info using SQL querys.

Raúl

Average of ratings: -
Re: Databases: How can I get enrolled users for a course using SQL
Try searching these forums. This question has been asked and answered many times.

Average of ratings: -
Re: Databases: How can I get enrolled users for a course using SQL

Thank you all for your help.

You were right Tim, I found an old post from you, next time I will do my homework better.

Regards

Raúl

Average of ratings: -
Re: Databases: How can I get enrolled users for a course using SQL

To get the enrolled user of a course you need this tables:
mdl_context, mdl_role, mdl_role_assignments

1. mdl_context... get records with contextlevel = CONTEXT_COURSE (CONTEXT_COURSE = 50) and instanceid = <id of course>
2. mdl_role... get record with shortname = 'student'
3. mdl_role_assignments... get records with contextid = <refer 1.> und roleid = <refer 2.>

In later versions you can also use the Moodle-API

1. with $context = get_context_instance(CONTEXT_COURSE, <id of course>) you get the Context 2.$students = get_role_users(5 , $context) (the 5 represents the role-id with role.shortname = 'student') happyMoodling, sven Average of ratings: Useful (4) Re: Databases: How can I get enrolled users for a course using SQL This maybe the most answered question from this forum: You join the tables user, role_assignments, context, role, course, course_categories if you need too. SELECT u.id, c.id, cc.id FROM mdl_user u INNER JOIN mdl_role_assignments ra ON ra.userid = u.id INNER JOIN mdl_context ct ON ct.id = ra.contextid INNER JOIN mdl_course c ON c.id = ct.instanceid INNER JOIN mdl_role r ON r.id = ra.roleid INNER JOIN mdl_course_categories cc ON cc.id = c.category --AND SUBSTRING( cc.path, 1, 2 ) = '/2' WHERE r.id =5 Average of ratings: Useful (1) Re: Databases: How can I get enrolled users for a course using SQL When joining through the context table in this case you'll have to be explicit about the context level, since many different things can have the same instanceid. INNER JOIN mdl_context ct ON ct.id = ra.contextid AND ct.contextlevel = 50 Average of ratings: - Re: Databases: How can I get enrolled users for a course using SQL here is a way to find everyone enrolled in a course:$contextid = get_context_instance(CONTEXT_COURSE, $COURSE->);$ssql = "SELECT u.id, u.username
FROM mdl_user u, mdl_role_assignments r
WHERE u.id=r.userid AND r.contextid = {$contextid->id}"; Average of ratings: Useful (1) Re: Databases: How can I get enrolled users for a course using SQL I havent been about to get this to work, are you sure it is correct? Average of ratings: - Re: Databases: How can I get enrolled users for a course using SQL Hi! This query should list all students and teachers of a course called 'your course shortname'. roleid = 5 and roleid =3 stand for the users (students and teachers) of the course! SELECT c.id AS id, c.fullname, u.username, u.firstname, u.lastname, u.email 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 c.shortname ='your course shortname' AND (roleid =5 OR roleid=3); hth Monica Average of ratings: - Re: Databases: How can I get enrolled users for a course using SQL Hi Sven, So let's say I want to get teachers within a course by API, ¿could I use this code?:$role = $DB->get_record('role', array('shortname' => 'teacher'));$context = get_context_instance(CONTEXT_COURSE, $courseid);$teachers = get_role_users($role->id,$context);
var_dump($teachers); Or may be by sql query: Hi Monica, Thanks for your post, I could get teachers within a course with: SELECT c.id AS id, c.fullname, u.username, u.firstname, u.lastname, u.email FROM mdl_role_assignments ra, mdl_user u, mdl_course c, mdl_context cxt, mdl_role r WHERE ra.userid=u.id AND ra.contextid=cxt.id AND cxt.contextlevel=50 AND cxt.instanceid=c.id AND c.shortname ='CursoPrueba' AND r.shortname = 'teacher'; Average of ratings: - Re: Databases: How can I get enrolled users for a course using SQL Function get_role_users() doesn't return anything. Average of ratings: - Re: Databases: How can I get enrolled users for a course using SQL Hi All: I am wondering if it is possible to use Monica's query above (or some other query) to display a course with the teacher and students in different columns? Something like: Course | Teacher Lastname | Student First Name| Student Last Name Not sure if this is feasible ? Since Teachers and Students are differentiated only by roleid ? Any help willbe highly appreciated. Thanks Karim Average of ratings: - Re: Databases: How can I get enrolled users for a course using SQL You should be able to output that with some pretty basic php. You need to create a .php page and use the query referenced in Monica post, then a basic combo of html and php to format the data in the page. Google "outputing a mysql query in php" Average of ratings: - Re: Databases: How can I get enrolled users for a course using SQL Hello: Displaying the query as is with php is not the problem. What I wanted to get is both the teachers and the students. The first using roleid =3 and the second using roleid =5... something like this: Course Fulname | Teacher Fulname | Student Fulname | .... I am not sure how to proceed to display simultaneously both the teachers and the students? Karim Average of ratings: - Re: Databases: How can I get enrolled users for a course using SQL karim - this is an example i cooked up. Two separate similar queries, one getting the teacher rec the other getting the participants. Using mysql_fetch_object to get pointer to that rec. detail, then using it in the loop that is outputting the participants. You can work on the formatting.$teacher = mysql_query(
"
SELECT c.id, c.fullname AS courseName,u.lastname AS lastName,r.name,r.id

FROM mdl_course c
JOIN mdl_context ct ON c.id = ct.instanceid
JOIN mdl_role_assignments ra ON ra.contextid = ct.id
JOIN mdl_user u ON u.id = ra.userid
JOIN mdl_role r ON r.id = ra.roleid

where c.id = 76 AND r.id = 3
"
);

$participants = mysql_query ( " SELECT c.id, u.lastname AS lastname,r.name,r.id FROM mdl_course c JOIN mdl_context ct ON c.id = ct.instanceid JOIN mdl_role_assignments ra ON ra.contextid = ct.id JOIN mdl_user u ON u.id = ra.userid JOIN mdl_role r ON r.id = ra.roleid where c.id = 76 AND r.id = 5 " );$teacherRec = mysql_fetch_object($teacher); echo"<p><b>Course Name Teacher Name Student Name</b></p>"; while ($row = mysql_fetch_array($participants)){ echo$teacherRec->courseName."         ".$teacherRec->lastName." " .$row["lastname"]."<br>";
}

Average of ratings: -
Re: Databases: How can I get enrolled users for a course using SQL

Thanks James, that should do it indeed.

I was thinking about using temporary tables to store the teacher and the students, mainly for performance reasons...

Karim

Average of ratings: -
Re: Databases: How can I get enrolled users for a course using SQL

This can never work if you don't even take into account deleted users.

Average of ratings: -
Re: Databases: How can I get enrolled users for a course using SQL

of course, to remove deleted users, just add :

AND u.deleted=0

Karim

Average of ratings: -
Re: Databases: How can I get enrolled users for a course using SQL

Hi Karim!

I'll change my query, haven't thought about that at the time I tested the query, because the deleted users are gone, as using LDAP the users are deleted anyway.

But in forums there the deleted users who posted something before are still available. But that's another problem.

Cheers,

Monica

Average of ratings: -
Re: Databases: How can I get enrolled users for a course using SQL

This code will return the user id's that are enrolled in course 2:

global $DB;$enrolled = $DB->get_records_sql(" SELECT c.id, u.id FROM {course} c JOIN {context} ct ON c.id = ct.instanceid JOIN {role_assignments} ra ON ra.contextid = ct.id JOIN {user} u ON u.id = ra.userid JOIN {role} r ON r.id = ra.roleid where c.id = 2");$count = count($enrolled); echo "There are$enrolled users in course 2";

ENJOY

Average of ratings: -
Re: Databases: How can I get enrolled users for a course using SQL

I'm using above query, but how can i use this for determing if the userid exists in the results?

example: userid 2 is the result. then hide everything for him, but if user 4 logs in show it instead..

Average of ratings: -