List all certificates issued - Needs Update

Re: List all certificates issued - Needs Update

by Rhonda Goetz -
Number of replies: 1
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: -