So you’ve set up your Moodle site and your courses are running smoothly.
Things couldn’t have been any better!
However, for some reason, you want to view a list of your students, and also view courses these students are enrolled in. You might want to export this data to another system, or it could purely be for backup purposes.
That’s exactly what happened with one of the members in our Facebook Community “Moodle Tips & Tricks“! He wanted to have a look at the names of his students along with the ability to view courses they’ve enrolled in.
So, after a discussion with our development team, we came up with a SQL query string for the same.
You can use the following SQL query:
SELECT CONCAT(u.firstname, ‘ ‘, u.lastname), GROUP_CONCAT(c.fullname) Course
FROM mdl_user u
JOIN mdl_role_assignments ra ON u.id = ra.userid
JOIN mdl_role r ON ra.roleid = r.id
JOIN mdl_context ctx ON ra.contextid = ctx.id AND ctx.contextlevel = 50
JOIN mdl_course c ON ctx.instanceid = c.id
WHERE r.archetype = ‘student’
GROUP BY u.id
Using this query, you’ll be able to view full-names of your students. This query will also let you view courses these students are enrolled in.