Início / user-18735247

thebaby's questions

Martin Hope
thebaby
Asked: 2023-12-31 08:29:07 +0800 CST
  • 5

Eu criei alguns campos personalizados do curso e quero criar alguns para mostrar alguns filtros que irão filtrar os cursos com base nas preferências do usuário, por exemplo, o filtro de idioma tem ENG, SP, IT

se o usuário escolher ENG, deverá exibir apenas os cursos que possuem ENG no idioma (campo customizado)

meu código:

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();
?>

erro:

Erro ao ler do banco de dados

Mais informações sobre este erro

Informações de depuração: coluna desconhecida 'cd.shortname' em 'cláusula where'

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',
)]

Código de erro: dmlreadexception

Notas: Algo que eu estava fazendo de errado estava usando o resultado da opção na consulta. Ex. as opções de idioma tinham ENG, SP, IT e eu estava usando na consulta a string ENG para filtrar esses cursos. ERRADO use o id da opção. Qualquer outra dica ou sugestão será mais que bem vinda

moodle