MySQL / PHP? List course users and show human readable date for first log entry

MySQL / PHP? List course users and show human readable date for first log entry

by Steve Bilton -
Number of replies: 7
Hi All,

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 :-
Course_id fullname user_id username firstname lastname email country firstaccess lastaccess lastlogin currentlogin timemodified
71 IOSH Managing Safely 13476 aaa Aaa aaaa aaaa@yahoo.com NG 0 1277751411 1277743064 1277748727 1276595533
71 IOSH Managing Safely 10555 aab Aab bbbb aaab@yahoo.co.uk 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 )

=(((@2-(0*3600))/86400)+25569)

So then the excel output will look like this :-
Course ID fullname User ID username firstname lastname email country FIRST ACCESS LAST ACCESS LAST LOGIN CURRENT LOGIN PROFILE MODIFIED
71 IOSH Managing Safely 13476 aaa Aaa aaaa aaaa@yahoo.com NG 01/01/1970 28/06/2010 28/06/2010 28/06/2010 15/06/2010
71 IOSH Managing Safely 10555 aab Aab
 bbbb bbbb@yahoo.co.uk GB 01/01/1970 28/06/2010 05/03/2010 28/06/2010 04/09/2009


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

Cheers
Steve
Average of ratings: -
In reply to Steve Bilton

Re: MySQL / PHP? List course users and show human readable date for first log entry

by sam marshall -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers
Um:

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.)

--sam

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...
Average of ratings: Useful (1)
In reply to sam marshall

Re: MySQL / PHP? List course users and show human readable date for first log entry

by Steve Bilton -
Hi Sam,

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.

Thanks

STeve
Average of ratings: -
In reply to Steve Bilton

Re: MySQL / PHP? List course users and show human readable date for first log entry

by sam marshall -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers
Too busy today, sorry - hopefully somebody else could help...
Average of ratings: -
In reply to Steve Bilton

Re: MySQL / PHP? List course users and show human readable date for first log entry

by Aaron Wells -
The simplest approach would be to add it as a subquery in the SELECT clause. Something like this:

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.

Cheers,
Aaron
Average of ratings: Useful (1)
In reply to Aaron Wells

Re: MySQL / PHP? List course users and show human readable date for first log entry

by Steve Bilton -
Hi,

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.

Thanks guys!

Steve
Average of ratings: -
In reply to Aaron Wells

Re: MySQL / PHP? List course users and show human readable date for first log entry

by chandrika harathi -

Hello,

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 ?

-Chandrika

Average of ratings: -
In reply to Steve Bilton

Odp: MySQL / PHP? List course users and show human readable date for first log entry

by Paweł Mucha -

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

Average of ratings: -