I've built the below SQL report via my localhost Moodle installation's phpmyadmin (MariaDB 10.4.8), which runs perfectly. However, this query encounters the 'not allowed words' error when attempting to save it in the Configurable Reports plug-in as the SQL reserved word 'update' is used within the two concatenated strings. How can I run this query through the Configurable Reports plug-in? I tried escaping update using backticks (ie '.../php?`update`=') like I would if the reserved word was a column name, but this doesn't fix the issue.
Here's the query:
-- displays all references to lynda in the choice options table and hyperlinks them to their Moodle edit page.
concat('<a target="_blank" href="%%WWWROOT%%/course/modedit.php?update=',cm.id,'">',cm.id,'</a>'),
concat('<a target="_blank" href="%%WWWROOT%%/course/modedit.php?update=',cm.id,'">',trim(cho.text),'</a>'),
from mdl_choice_options cho
join mdl_course_modules cm on cm.instance = cho.choiceid and cm.module = 5
join mdl_choice ch on cho.choiceid = ch.id
where cho.text like '%lynda%'
I've uploaded a screenshot of the report in the Configurable Reports plug-in and phpmyadmin. Any help is much appreciated!