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

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

by Raúl Castellanos -
Number of replies: 20
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.

Thanks in advance

Raúl
Average of ratings: -
In reply to Raúl Castellanos

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

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
Try searching these forums. This question has been asked and answered many times.
Average of ratings: -
In reply to Tim Hunt

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

by Raúl Castellanos -
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: -
In reply to Raúl Castellanos

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

by Sven Trümper -
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 (6)
In reply to Raúl Castellanos

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

by Ângelo Rigo -
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)
In reply to Ângelo Rigo

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

by John Ehringer -
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: -
In reply to Ângelo Rigo

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

by Thea Ganoe -
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)
In reply to Thea Ganoe

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

by Paul 'Fire' Preibisch -
I havent been about to get this to work, are you sure it is correct?
Average of ratings: -
In reply to Paul 'Fire' Preibisch

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

by Monica Franz -

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: -
In reply to Monica Franz

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

by Alejandro Michavila Pallarés -
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: -
In reply to Monica Franz

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

by karim faid -

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: -
In reply to karim faid

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

by james mergenthaler -

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: -
In reply to james mergenthaler

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

by karim faid -

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: -
In reply to karim faid

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

by james mergenthaler -

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: -
In reply to james mergenthaler

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

by karim faid -

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: -
In reply to james mergenthaler

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

by Deleted user -

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

Average of ratings: -
In reply to Deleted user

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

by karim faid -

of course, to remove deleted users, just add :

AND u.deleted=0

 

Karim

Average of ratings: -
In reply to karim faid

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

by Monica Franz -

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: -
In reply to james mergenthaler

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

by Andrew Normore -

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 smile

Average of ratings: Useful (1)
In reply to Andrew Normore

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

by Erwin Kroontje -

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: Useful (1)