Hi All, I wonder if somebody more expert than I at MySQL would be able to help? My query below lists everybody on the site, their courses and when accessed first and last. The output is:
user.name | Steve | Smith | 13/08/2015 | 25/02/2016 | Course name | student |
What I would like to add is a list of categories, is this possible?
Tom
SELECT
user2.username AS Username,
user2.firstname AS Firstname,
user2.lastname AS Lastname,
DATE_FORMAT(FROM_UNIXTIME(firstaccess),'%Y-%m-%d') AS CourseFirstAccess,
DATE_FORMAT(FROM_UNIXTIME(lastaccess),'%Y-%m-%d') AS CourseLastAccess,
course.fullname AS Course
,(SELECT shortname FROM mdl_role WHERE id=en.roleid) AS ROLE
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 user2 ON ue.userid = user2.id