I was hoping someone can help me out here.
I'm ssing moodle 1.8.8, LAMP server.
AIM: To show all users of a course displaying the 'first log entry time' in a list.
To break it down :-
List the users of a specified course with Role IDs: 5, 24
Show the first log entry 'time/date' next to each user from the mdl_log table.
Users are from a specified Course ID: 71
Output user details in a list with first log entry to the course
So far I have some MySQL that will output user details found in the mdl_course and mdl_user table using table joins.
The Time fields in the mdl_user table consist of 'firstaccess' , 'lastaccess', 'lastlogin', 'currentlogin'. However I notice the first access fields are almost all unpopulated (value=0). As a time stamp to human readable this would read '1st Jan 1970' for each user, which is no good.
I need to output each users first access to a specific course and it doesn't seem I can use the firstaccess/login or other time fields for this.
I used the below MySQL to output users of a course and some user details including the time fields. Here's the code :
#Command to show students from a particular course for 1.8.8 Moodle for multiple roles - include user access details
SELECT c.id, c.fullname, u.id, u.username, u.firstname, u.lastname, u.email, u.country, u.firstaccess, u.lastaccess, u.lastlogin, u.currentlogin, u.timemodified
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'
OR ra.roleid = '24'
LEFT OUTER JOIN mdl_user u ON ra.userid = u.id
WHERE cx.contextlevel = '50'
AND c.id = '71'
ORDER BY u.username
LIMIT 0 , 2000
As you can see this will output the following fields :-
|71||IOSH Managing Safely||13476||aaa||Aaafirstname.lastname@example.org||NG||0||1277751411||1277743064||1277748727||1276595533|
|71||IOSH Managing Safely||10555||aab||Aabemail@example.com||GB||0||1277726522||1267767065||1277716384||1252074863|
If I output this as a .csv and load in to Excel I can then use the following equation to convert this in to human readable time (columns formatted in dd/mm/yyyy) :
( where '@' = the column letter | and formatted in dd/mm/yyyy format )
So then the excel output will look like this :-
|Course ID||fullname||User ID||username||firstname||lastname||country||FIRST ACCESS||LAST ACCESS||LAST LOGIN||CURRENT LOGIN||PROFILE MODIFIED|
|71||IOSH Managing Safely||13476||aaa||Aaafirstname.lastname@example.org||NG||01/01/1970||28/06/2010||28/06/2010||28/06/2010||15/06/2010|
|71||IOSH Managing Safely||10555||aab||Aab
Thing is this doesn't show me what I need as the values in first access and other time fields are for the most part set to = 0. I can't use the other time fields either.
It seems that moodle doesn't store a users first access unless the user has created the account using self based email authentication (unless i'm mistaken), so the first access field doesn't get populated with the time in seconds and is always = 0.
So the only way to get the details I need is from the mdl_log table.
I need to parse through the logs until I find the very first entry for a user ID within the specified course and list this date next to each course user outputted as a list. I can then convert the dates using the same method in an Excel document.
Thing is, i'm not sure how to do that. I think i'll have to have some PHP to go through each row for each user ID to find the lowest time entry for each user within the specified course.
This would then allow me to display the whole list of course users and accurately show a First Course Access time field next to each user, along with some additional users details, i.e. email, firstname lastname etc..
Can any one help me out here please, i'm finding this a bit tricky.
Thank you in advance
select userid, min(time) from mdl_log where course=1234 group by userid
From memory, but won't this work. What am I missing?
(You can join in the other fields to restrict it to the right role assignments, and get the names etc. For example the query I gave above could be a subquery in the FROM clause of a larger query.)
PS On a large system, this query will probably be slow, but you might be OK with it from the point of view of a report that is run rarely and especially it's probably fine on a small system. I ran this for one of our courses but cancelled it after it took over a minute...
PPS Any query such as this based on the log table will of course not give reliable 'first use' answers if you have log archiving enabled and set to less than the life of the course...
Thanks for your reply, that really helped. I'm still struggling to do the table joins and output the user details I require aswell as the first log entry.
How would I use this as a subquery in the FROM clause? I'm a bit rusty with MySQL.
select c.id as courseid, c.fullname as coursename, u.id as userid, u.username as username, u.firstname as firstname, u.lastname as lastname, u.email as email, u.country as country, ( select min(log.time) from mdl_log log where log.userid = u.id and log.course = c.id ) as first_course_access 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 left outer join mdl_user u on ra.userid = u.id where ra.roleid in (5, 24) and cx.contextlevel = 50 and c.id = 71 order by u.username
That'll give you the user's earliest logged access to the course, or a NULL if there's no record of them accessing the course.
Thank you both for your replies!!
The query from Aaron gave me exactly what I needed, your an absolute star!!
I think i'm getting to grips with the more complex MySQL statements thanks to your assistance.
I am trying to get a simple query:
List all the participants/students in a course. I can use Aron's query by replacing the courseid with mine.
What are the roleid / contextevel ? what should these values be to simply list all students in a course ?
One more thing. I was searching how to "encode" the data fields in query to show it in Configurable Reports Block and there is a solution you must use
FROM_UNIXTIME(u.lastaccess)in SQL and then u will have dd/mm/yy hh/mm/ss date field. You dont have to use Exel.
Other interesting time function in MySQL you can find there: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html