With the help of my IT person, I was able to write this code to get information on who has received their certificate. The issue I am having however is the course names in the report appear to be wrong and one of my criticle courses is missing from the report. Does anyone know how to modify this so that I get all of my courses, the students name, address, company, when they recieved their certificate and the verification code for the cert?
SELECT
concat(mdl_user.firstname, ' ', mdl_user.lastname) AS Name,
(select data from mdl_user_info_data a where fieldid = 3 and userid = mdl_user_info_data.userid ) as company,
mdl_course.shortname AS Coursename,
mdl_certificate.name AS Certificate,
mdl_certificate_issues.code AS VerificationCode,
DATE_FORMAT( FROM_UNIXTIME(mdl_certificate_issues.timecreated), '%m/%d/%y' ) AS DATE,
mdl_grade_grades.finalgrade,
(select data from mdl_user_info_data a where fieldid = 5 and userid = mdl_user_info_data.userid ) as address,
mdl_user.city,
(select data from mdl_user_info_data a where fieldid = 7 and userid = mdl_user_info_data.userid ) as state,
(select data from mdl_user_info_data a where fieldid = 8 and userid = mdl_user_info_data.userid ) as zip,
mdl_user.country
FROM
mdl_certificate_issues
INNER JOIN mdl_user_info_data
ON mdl_certificate_issues.userid = mdl_user_info_data.userid
INNER JOIN mdl_certificate
ON mdl_certificate_issues.certificateid = mdl_certificate.id
INNER JOIN mdl_course
ON mdl_certificate_issues.certificateid = mdl_course.id
INNER JOIN mdl_user on mdl_certificate_issues.userid = mdl_user.id
INNER JOIN mdl_grade_grades on mdl_certificate_issues.userid = mdl_grade_grades.id