Incorrect number of query parameters in Moodle *How to get the external links from course page

Question

My Dear I tried to run the following ad-hoc database query in Moodle but i got it error

there any way to fix it , thanks

*My target to find all the external links (URL) from course page

SELECT
        concat('<a target="_new" href=%%WWWROOT%%/course/view.php?id=',
                c.id, '">', c.fullname, '</a>') AS Course
        ,c.shortname,r.name
        ,(
            SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
            FROM prefix_role_assignments AS ra
                JOIN prefix_context AS ctx ON ra.contextid = ctx.id
                JOIN prefix_user AS u ON u.id = ra.userid
            WHERE ra.roleid = 3 
            AND ctx.instanceid = c.id 
            LIMIT 1
          ) AS Teacher
        ,concat('<a target="_new" href="%%WWWROOT%%/mod/resource/view.php?id=',
                r.id, '">', r.name, '</a>') AS Resource
FROM prefix_resource AS r
    JOIN prefix_course AS c ON r.course = c.id
WHERE r.reference LIKE 'https://stackoverflow.com/%'    

Error message :

" Error when executing the query: ERROR: Incorrect number of query 
parameters. Expected 2, got 0"

Answer 1

When executing SQL statements in Moodle, any '?' characters are used to indicate placeholders for parameters that need to be provided along with the query.

From the style of your query, I'm assuming you're using report_customsql or block_configurablereports for your query, so you won't have the option of providing such parameters.

If you look at the documentation here: https://docs.moodle.org/en/Custom_SQL_queries_report you will see that the workaround for this is to replace any '?' characters in your query with '%%Q%%'.

So the fixed query should look like:

SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php%%Q%%id=',c.id,'">',c.fullname,'</a>') AS Course,
    c.shortname,r.name, (SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher,
concat('<a target="_new" href="%%WWWROOT%%/mod/resource/view.php%%Q%%id=',r.id,'">',r.name,'</a>') AS Resource
FROM prefix_resource AS r
JOIN prefix_course AS c ON r.course = c.id
WHERE r.reference LIKE 'https://stackoverflow.com/%'