Kate one of our Technology Enhanced Learning Advisors (TERA) asked if I could provide her with details of how many staff in her faculty are using quizzes, Moodle and Turnitin assignments in this academic year. We do not have any reports to hand I do have access to a copy of the Moodle database. Of course, where there is a MySQL server and a Moodle database I should be able to provide Kate with the details she needs to support her academic colleagues.
Thanks to the work of Prof Mark Stubbs and the EQAL team modelling how units and courses are represented in the University, and ensuring a consistent naming structure; our Moodle Short name contains all the information I need to be able to construct SQL select statements.
Our Moodle shortname has the following structure
So for example, a unit code may be 399Z9999_1415_9Z3F and as our unit codes are mapped to the faculty by the first letter of the unit code (in most instances, we do have one department that has moved Faculties and it breaks this simple rule)
Using our example shortname we can construct a select statement using like to return only those shortnames that belong to the faculty in question and for the year 1415
The example below returns Moodle assignments ordered by the date the assignment is due
Database. Is used below as the name of the Moodle database
SELECT database_course.shortname, database_course.fullname, database_assign.course, database_assign.intro, from_unixtime(database_assign.duedate) as datedue, database_course.id
FROM database_assign, database_course
where database_course.shortname like ‘%1415%’ and database_course.id=database_assign.course
order by datedue limit 0,10000;
The select statement is almost identical for quizzes
SELECT database_course.shortname, database_course.fullname ,database_quiz.course, database_quiz.intro, from_unixtime(database_quiz.timeopen) as quiztime, database_course.id
FROM database_quiz, database_course where database_course.shortname like ‘3%1415%’ and database_course.id=database_quiz.course
order by quiztime limit 0, 5000;
Turnitin is more of an issue , Turnitin assignments can have one or more parts, to allow for this as well as having the turnitintool table there is a turnitintool_parts table. Each assignment part may have their own due date which may be different from the due date in the main turnitintool table. If there is only one part the SQL is very familiar with the addition of checking of the number of parts.
SELECT database_course.shortname, database_course.fullname ,database_turnitintool.course, database_turnitintool.name, from_unixtime(database_turnitintool.defaultdtdue) as duedate , database_course.id
FROM mmu01moodle.database_turnitintool, database_course where database_course.shortname like ‘3%1415%’ and database_course.id=database_turnitintool.course and numparts=1
order by duedate limit 0, 5000;