- course ID
- course role(s)
Many thanks in advance of your extremely helpful answers,
Sami
1) I would like to create a function that when given a user ID, outputs the following data:
Many thanks in advance of your extremely helpful answers,
Sami
Many thanks in advance of your extremely helpful answers,
Sami
In addition to points 1 and 2 from above I have excluded hidden courses/categories and sorted the results by category.
SQL:
SELECT DISTINCT c.id, c.fullname, c.shortname, c.category, cc.name
FROM mdl_course AS c
JOIN mdl_context AS ctx ON c.id = ctx.instanceid
JOIN mdl_role_assignments AS ra ON ra.contextid = ctx.id
JOIN mdl_user AS u ON u.id = ra.userid
JOIN mdl_course_categories AS cc ON cc.id = c.category
WHERE u.id = [insert user id] AND c.visible = 1 AND cc.visible = 1
ORDER BY cc.name ASC
SQL:
SELECT DISTINCT c.id, c.fullname, c.shortname, c.category, cc.name
FROM mdl_course AS c
JOIN mdl_context AS ctx ON c.id = ctx.instanceid
JOIN mdl_role_assignments AS ra ON ra.contextid = ctx.id
JOIN mdl_user AS u ON u.id = ra.userid
JOIN mdl_course_categories AS cc ON cc.id = c.category
WHERE u.id = [insert user id] AND c.visible = 1 AND cc.visible = 1
ORDER BY cc.name ASC