List all certificates issued - Needs Update

Re: List all certificates issued - Needs Update

by Randy Thornton -
Number of replies: 4
Picture of Documentation writers

I had same issue with that one too.

I modified it a bit, and took out the WHERE clause, but don't have enough real certificates to test thoroughly; worked okay on my sample data. It is much simpler (uglier) version that yours.

SELECT u.username, c.shortname, t.name, l.timecreated
FROM prefix_certificate_issues l
JOIN prefix_user u ON l.userid = u.id
JOIN prefix_certificate t ON l.certificateid = t.id
JOIN prefix_course c ON t.course = c.id
ORDER BY u.username, c.shortname

This works on the Simple Certificate module as well, if you just change the table names to _simplecertificate and _simplecertificate_issues. Same field names mostly.

- Randy

Average of ratings: -
In reply to Randy Thornton

Re: List all certificates issued - Needs Update

by Glenn Sisson -

The query works for me but I need the timecreated column to be in date format. 

Current query:


SELECT u.firstname, u.lastname, l.timecreated

FROM prefix_certificate_issues l

JOIN prefix_user u ON l.userid = u.id

JOIN prefix_certificate t ON l.certificateid = t.id

JOIN prefix_course c ON t.course = c.id

ORDER BY  l.timecreated


I have tried:

SELECT u.firstname, u.lastname, l.timecreated

DATE_FORMAT(FROM_UNIXTIME(l.timecreated),'%Y-%m-%d') AS Certificate Date

FROM prefix_certificate_issues l

JOIN prefix_user u ON l.userid = u.id

JOIN prefix_certificate t ON l.certificateid = t.id

JOIN prefix_course c ON t.course = c.id

ORDER BY  l.timecreated


but get an error. 

I would also like to add a custom user profile field with shortname of RACGP.


Any help would be appreciated.

Using Moodle 2.7



Average of ratings: -
In reply to Glenn Sisson

Re: List all certificates issued - Needs Update

by Randy Thornton -
Picture of Documentation writers

Here's basic code for how to do custom profile fields:

SELECT u.username, uif.name, uid.data
FROM prefix_user AS u
JOIN prefix_user_info_data AS uid ON uid.userid = u.id
JOIN prefix_user_info_field AS uif ON uid.fieldid = uif.id


You are getting an error because you are missing a comma between select fields, put one right before DATE_FORMAT.

Average of ratings: -
In reply to Randy Thornton

Re: List all certificates issued - Needs Update

by Rhonda Goetz -
I am tearing my hair out.  I like my hair...

I am using this code for generating a Certificate report in Moodle 3.0

SELECT u.firstname, u.lastname, l.timecreated

DATE_FORMAT(FROM_UNIXTIME(l.timecreated),"%Y-%m-%d") AS Certificate Date,

FROM prefix_certificate_issues l

JOIN prefix_user u ON l.userid = u.id

JOIN prefix_certificate t ON l.certificateid = t.id

JOIN prefix_course c ON t.course = c.id

ORDER BY  l.timecreated

But keep getting this error with the timestamp.  I've added commas and removed them to no avail.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(FROM_UNIXTIME(l.timecreated),"%Y-%m-%d") AS Certificate Date,

FROM wpq_certi' at line 3
Can anyone direct me?  I need a nicely formatted Certificate report.
Average of ratings: -
In reply to Rhonda Goetz

Re: List all certificates issued - Needs Update

by Randy Thornton -
Picture of Documentation writers

Rhonda,

As I mentioned above, the error in this code is there is no comma after l.timecreated before DATE_FORMAT() in the SELECT statement. There is also an extra comma at the end of it before the FROM. Those are basic syntax errors.

The working code should be:

SELECT
u.firstname,
u.lastname,
l.timecreated,
DATE_FORMAT(FROM_UNIXTIME(l.timecreated),"%Y-%m-%d") AS CertificateDate
FROM etc etc

Average of ratings: -