SQL Users by category and course report

SQL Users by category and course report

by Tom Denton -
Number of replies: 2

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

Average of ratings: -
In reply to Tom Denton

Re: SQL Users by category and course report

by Josh Willcock -
Picture of Core developers Picture of Plugin developers
Are you trying to just output the category for each course? Or are you looking for something more intelligent?


If you just wanted Course X sits in Category Y that is straight forward using another join which I have added below, however if you are looking for Course X sits in Site->Category C->Category M->Category Y (including the full trail of categories) this would be better to do using something like PHP rather than pure MySQL?


SELECT
user2.username AS Username,
user2.firstname AS Firstname,
user2.lastname AS Lastname,
category.name AS Category,
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_course_categories as category ON course.category
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


Average of ratings: Useful (2)
In reply to Josh Willcock

Re: SQL Users by category and course report

by Tom Denton -

Thanks Josh, this is all I need for now - should have done it myself but I lost confidence wink

Much appreciated

FYI and anybody who may make use of it, and like me is a MySQL novice, I export as csv, open it in Excel set the data as tables and it allows lots of useful figures such as who accessed what and when etc. by filtering

Tom

Average of ratings: Useful (1)