About Custom Fields of Moodle

Question

I am using Moodle 3.11.

I have a custom course field named discount.

how can i fetch names of all the course that provides discount on a new page?

Answer 1

You could use this query for Moodle - replace d.value with the required field

$sql = 'SELECT c.id AS courseid, c.fullname AS coursename, d.value AS discount
    FROM mdl_customfield_category cat
    JOIN mdl_customfield_field f ON f.categoryid = cat.id AND f.shortname = :shortname
    JOIN mdl_customfield_data d ON d.fieldid = f.id
    JOIN mdl_course c ON c.id = d.instanceid
    WHERE cat.area = :area'

$courses = $DB->get_records_sql($sql, array('area' => 'course' 'shortname' => 'discount'));

for each ($courses as $course) {
    // Display $course->fullame and $course->discount
}

Or this for Totara

$sql = 'SELECT c.id AS courseid, c.fullname, d.data AS discount
        FROM {course_info_field} f
        JOIN {course_info_data} d ON d.fieldid = f.id AND d.data > ''
        JOIN {course} c ON c.id = d.courseid
        WHERE f.shortname = :shortname'

$courses = $DB->get_records_sql($sql, array('shortname' => 'discount'));

for each ($courses as $course) {
    // Display $course->fullame and $course->discount
}