General developer forum

 
 
Picture of Raúl Castellanos
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.

Thanks in advance

Raúl
 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: Databases: How can I get enrolled users for a course using SQL
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers
Try searching these forums. This question has been asked and answered many times.
 
Average of ratings: -
Picture of Raúl Castellanos
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: -
Picture of Sven Trümper
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 (1)
At work
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: -
Picture of John Ehringer
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: -
Picture of Thea Ganoe
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: -
Picture of Paul 'Fire' Preibisch
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: -
Picture of Monica Franz
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: -
Picture of Alejandro Michavila Pallarés
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: -
Picture of Alejandro Michavila Pallarés
Re: Databases: How can I get enrolled users for a course using SQL
 
Function get_role_users() doesn't return anything.
 
Average of ratings: -
Picture of karim faid
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: -
Picture of james mergenthaler
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: -
Picture of karim faid
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: -
Picture of james mergenthaler
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: -
Picture of karim faid
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: -
Picture of Ries Twisk
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: -
Picture of karim faid
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: -
Picture of Monica Franz
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: -
Andrew Normore
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 smile

 
Average of ratings: -
Picture of Erwin Kroontje
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: -