How to display courses only based on tag and user interests?
Re: How to display courses only based on tag and user interests?
Hi,
I think this will help you
LIST OF "ALL USERS" ASSIGNED TO A COURSE (INCLUDING TEACHERS)
QUERY: `SELECT u.id,course.id as course FROM mdl_course AS course JOIN mdl_enrol AS en ON en.courseid = course.id JOIN mdl_user_enrolments AS ue ON ue.enrolid = en.id JOIN mdl_user AS u ON ue.userid = u.id where course.id = 36`;
LIST OF "STUDENTS" ASSIGNED TO A COURSE
QUERY: `SELECT DISTINCT u.id AS userid, c.id AS courseid FROM mdl_user u JOIN mdl_user_enrolments ue ON ue.userid = u.id JOIN mdl_enrol e ON e.id = ue.enrolid JOIN mdl_role_assignments ra ON ra.userid = u.id JOIN mdl_context ct ON ct.id = ra.contextid AND ct.contextlevel = 50 JOIN mdl_course c ON c.id = ct.instanceid AND e.courseid = c.id JOIN mdl_role r ON r.id = ra.roleid AND r.shortname = 'student' WHERE e.status = 0 AND u.suspended = 0 AND u.deleted = 0 AND (ue.timeend = 0 OR ue.timeend > NOW()) AND ue.status = 0`;
LIST OF USERS WHO COMPLETED THE COURSE
QUERY: `SELECT u.id,u.username, c.shortname, DATE_FORMAT(FROM_UNIXTIME(cc.timecompleted ),'%Y-%m-%d') AS completed FROM mdl_course_completions AS cc JOIN mdl_course AS c ON cc.course = c.id JOIN mdl_user AS u ON cc.userid = u.id WHERE c.enablecompletion = 1 AND c.id=36 ORDER BY u.id`;
Thanks