Hi there,
over the summer, when we migrate to 2.2, there is going to be a short time where some of our users have to be taking their courses in 1.9.x and also in 2.2.x.
I was thinking of creating a landing page where after they login (in version 2.2.x ideally), it would show them a generic php page in 2.2.x of all their courses on both platforms.
I was thinking of simply writing a query to connect to both datasources on different machines and present it to them. They then click to go to their courses.
My questions are:
What would the sql statement be to get student enrolments for versions 1.9 and 2.2.x?
How could I get their userid from the page and use that as part of the "where" clause to further filter them only?
Here is what I got so far for version 1.9.x
SELECT c.id, c.fullname, u.firstname, u.lastname, u.email, u.id
FROM mdl_course c LEFT OUTER JOIN mdl_context cx ON c.id = cx.instanceid LEFT OUTER JOIN mdl_role_assignments ra ON cx.id = ra.contextid AND ra.roleid = '5' LEFT OUTER JOIN mdl_user u ON ra.userid = u.id
WHERE cx.contextlevel = '50'
AND u.id = = ' where/how do I get their id after they log in?'
And for version 2.2.x? is????
SELECT mdl_course.id, mdl_course.category, mdl_course.shortname, mdl_course.fullname, mdl_course.timecreated, mdl_course.timemodified, mdl_course_categories.id, mdl_course_categories.name, mdl_role_assignments.userid, mdl_role_assignments.roleid, mdl_user.firstname, mdl_user.lastname, mdl_user.email, mdl_user_enrolments.enrolid, mdl_user_enrolments.userid
FROM mdl_course, mdl_course_categories, mdl_role_assignments, mdl_user, mdl_user_enrolments
WHERE mdl_user.id = mdl_role_assignments.userid
and
mdl_user.id = mdl_user_enrolments.userid
and
mdl_course.category = mdl_course_categories.id
and
mdl_role_assignments.roleid = '5';
and
mdl_user.id = ' where/how do I get their id after they log in?'
and
mdl_role_assignments.userid = = ' where/how do I get their id after they log in?'
Could anyone help me with rewriting the version 2.2.x using joins instead? Joins are more efficient no?
Thank you.