How can I find the current user's expiry date on the current course?
I've started writing a block which is intended to do this but I'm struggling with the SQL.
$sql = "SELECT enrol.enrolstartdate, enrol.enrolenddate FROM {enrol}<br />
INNER JOIN {user_enrolment}
ON user_enrolment.enrolid= enrol.id
WHERE user_enrolment.userid=? AND enrol.courseid=?";
$result = $DB->get_record_sql($sql, array($USER->id, $COURSE->id) );
This doesn't work.
Any ideas anyone please?
Cheers
Dave
Hello Dave,
If you’ve set the expiry date of the enrolment for students, you’ll get the expiry date from mdl_user_enrolments table with a column named "timeend" with respect to each other. And if you’ve set the enrolment period for course, you’ll get the expiry date from mdl_enrol table with a column named "enrolperiod".
The query will look like:
$sql = "select mdl_enrol.enrolperiod as course_expiry_date, mdl_user_enrolments.timeend as user_course_expiry_date from mdl_enrol join mdl_user_enrolments on mdl_user_enrolments.enrolid = mdl_enrol.id where mdl_courseid=? and mdl_user_enrolments.userid=?";
I hope this’ll help you to resolve your issue.
Cheers!
Purnendu
DualCube
Hi DualCube
Thanks for that. I've tried the above and I get "Error reading from database". My tables don't begin with mdl_ - I've tried replacing with my prefix (m246) but to no avail.
It’s our pleasure to share your problem which actually turns into a learning process. I would suggest you to first run the mysql query in the SQL console with your table prefix for a particular course and user id and check whether the SQL query is working properly. If the query is working properly, turn on the debugging in your Moodle site from the sitemap given below:
site administration -> development -> debugging
This will describe the error "error reading from database" in more details. Kindly provide us the error screenshot so that we can give you the exact solution.
Till then, keep on Moodling!
Purnendu
DualCube