基于下拉自定义字段的 Moodle 过滤课程

我已经创建了一些课程自定义字段，我想创建一些来显示一些过滤器，这些过滤器将根据用户的偏好过滤课程，例如过滤器语言有 ENG、SP、IT

如果用户选择 ENG，则应仅显示语言中包含 ENG 的课程（自定义字段）

我的代码：

require_once(__DIR__ . '/../config.php');

$PAGE->set_context(context_system::instance());
$PAGE->set_pagelayout('standard');
$PAGE->set_title("Custom Fields");
$PAGE->set_heading("All Custom Fields for Courses");

echo $OUTPUT->header();

$ftopics = $DB->get_records('customfield_field', array('shortname' => 'topic'));
$flevels = $DB->get_records('customfield_field', array('shortname' => 'level'));
$flanguages = $DB->get_records('customfield_field', array('shortname' => 'language'));

echo '<form method="get" action="#">'; // Start a form

// ----- TOPIC OPTIONS -----
echo '<select name="topic_filter" id="topic_filter">'; // Start a dropdown/select element
echo '<option value="">All Topics</option>'; // Include an option for all topics

foreach ($ftopics as $topic) {
    $configdata = json_decode($topic->configdata, true);

    // Check if 'options' key exists in the decoded configdata
    if (isset($configdata['options'])) {
        // Split the options string into an array using "\r\n" as the delimiter
        $options = explode("\r\n", $configdata['options']);

        // Remove empty values
        $options = array_filter($options);

        // Display options as dropdown options
        foreach ($options as $option) {
            echo '<option value="' . $option . '">' . $option . '</option>';
        }
    }
}

echo '</select>'; // End the dropdown/select element

// ----- LEVEL OPTIONS -----
echo '<select name="level_filter" id="level_filter">';
echo '<option value="">All Levels</option>'; // Include an option for all levels

foreach ($flevels as $level) {
    $configdata = json_decode($level->configdata, true);

    // Check if 'options' key exists in the decoded configdata
    if (isset($configdata['options'])) {
        // Split the options string into an array using "\r\n" as the delimiter
        $options = explode("\r\n", $configdata['options']);

        // Remove empty values
        $options = array_filter($options);

        // Display options as dropdown options
        foreach ($options as $option) {
            echo '<option value="' . $option . '">' . $option . '</option>';
        }
    }
}

echo '</select>'; // End the dropdown/select element

// ----- LANGUAGE OPTIONS -----
echo '<select name="language_filter" id="language_filter">';
echo '<option value="">All Languages</option>'; // Include an option for all languages

foreach ($flanguages as $language) {
    $configdata = json_decode($language->configdata, true);

    // Check if 'options' key exists in the decoded configdata
    if (isset($configdata['options'])) {
        // Split the options string into an array using "\r\n" as the delimiter
        $options = explode("\r\n", $configdata['options']);

        // Remove empty values
        $options = array_filter($options);

        // Display options as dropdown options
        foreach ($options as $option) {
            echo '<option value="' . $option . '">' . $option . '</option>';
        }
    }
}

echo '</select>'; // End the dropdown/select element

echo '<input type="submit" value="Filter">';
echo '</form>'; // End the form

// Fetch filter values
$topicFilter = optional_param('topic_filter', null, PARAM_TEXT);
$levelFilter = optional_param('level_filter', null, PARAM_TEXT);
$languageFilter = optional_param('language_filter', null, PARAM_TEXT);

// Construct SQL query based on the selected filters
$sql = "SELECT c.id, c.fullname, c.summary
        FROM {course} c";

// Add WHERE clause based on selected filters
$whereClause = array();
if (!empty($topicFilter)) {
    $whereClause[] = "c.id IN (SELECT cd.instanceid FROM {customfield_data} cd WHERE cd.shortname = 'topic' AND cd.value = :topic_filter AND cd.instanceid = c.id)";
}
if (!empty($levelFilter)) {
    $whereClause[] = "c.id IN (SELECT cd.instanceid FROM {customfield_data} cd WHERE cd.shortname = 'level' AND cd.value = :level_filter AND cd.instanceid = c.id)";
}
if (!empty($languageFilter)) {
    $whereClause[] = "c.id IN (SELECT cd.instanceid FROM {customfield_data} cd WHERE cd.shortname = 'language' AND cd.value = :language_filter AND cd.instanceid = c.id)";
}

// Combine WHERE clauses with AND
if (!empty($whereClause)) {
    $sql .= ' WHERE ' . implode(' AND ', $whereClause);
}

// Execute the query
$params = array(
    'topic_filter' => $topicFilter,
    'level_filter' => $levelFilter,
    'language_filter' => $languageFilter
);

$courses = $DB->get_records_sql($sql, $params);

// Display the titles of the filtered courses
foreach ($courses as $course) {
    echo '<div>';
    echo '<h2>' . html_writer::link(new moodle_url('/course/view.php', array('id' => $course->id)), $course->fullname) . '</h2>';
    echo '<p>' . $course->summary . '</p>';
    echo '</div>';
}

echo $OUTPUT->footer();
?>

错误：

从数据库读取时出错

有关此错误的更多信息

调试信息：“where 子句”中的未知列“cd.shortname”

SELECT c.id, c.fullname, c.summary
FROM mdl_course c WHERE c.id IN (SELECT cd.instanceid FROM mdl_customfield_data cd WHERE cd.shortname = 'level' AND cd.value = ? AND cd.instanceid = c.id)
[array (
0 => 'HEI',
)]

错误代码：dmlreadException

注意： 我做错了一些事情，我在查询中使用了选项的结果。例如，语言选项有 ENG、SP、IT，我在查询中使用字符串 ENG 来过滤这些课程。选项 ID 的使用错误。任何其他提示或建议，非常欢迎