Error with ad-hoc query

Error with ad-hoc query

by Ahmed Nobani -
Number of replies: 5

I can't create a query with hyperlink result

this is the error: Error when executing the query: ERROR: Incorrect number of query parameters. Expected 1, got 0.

The query:

SELECT CONCAT('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course

FROM prefix_course AS c

WHERE c.id = 55


Thanks  in advance


Average of ratings: -
In reply to Ahmed Nobani

Re: Error with ad-hoc query

by Davo Smith -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
At a guess, the "?" character in the URL is being interpreted as a parameter to be passed into the query. But the code isn't passing in any parameters, so Moodle is complaining.

The documentation here: https://docs.moodle.org/310/en/Custom_SQL_queries_report#Students_that_are_in_a_course_where_groups_are_turned_on.2C_but_have_not_been_assigned_to_a_group_yet
would imply that you can use %%Q%% to get around this issue, but I've never tried it myself.
Average of ratings: -
In reply to Ahmed Nobani

Re: Error with ad-hoc query

by Ken Task -
Picture of Particularly helpful Moodlers

Doesn't the target attribute come after the <a href=proto

See: https://www.w3schools.com/tags/att_a_target.asp

Do know it's tricky to get ticks + quotes (literals) interp correctly! :|

'SoS', Ken


Average of ratings: -
In reply to Ken Task

Re: Error with ad-hoc query

by Davo Smith -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
Ken,

In this case I don't think that is true - HTML tag attributes can generally be in any order.

It is also not really relevant, as the error entirely relates to the "?" character being interpreted as a database query parameter (for which no value has been supplied).
Average of ratings: -
In reply to Davo Smith

Re: Error with ad-hoc query

by Ken Task -
Picture of Particularly helpful Moodlers

You are correct Davo!  That's what I get for thinking off the top! :|

I have similar only built in webmin's custom tools for a link to a course.

Finally 'hardcoded' the url to site:

,concat('<a target="_new" href="https://hardcoded/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course

so what you are saying ops query isn't pulling the c.id value above - me thinks.

'SoS', Ken

Average of ratings: -
In reply to Ahmed Nobani

Re: Error with ad-hoc query

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
Because of how Moodle database query system works, you cannot use either a : or ? in they query. For convenience you can use %%C%% and %%Q%% in strings, and they will get replaced with : and ? in the results.

Also, you don't need to generate HTML. That won't work (it will get escaped). Instead, have two columns in your query:

SELECT fullname AS course,
CONCT('%%WWWROOT%%/course/view.php%%Q%%id=', c.id) AS course_link_url,

and in the results that will be turned into one column that is a link.

Finally, if you use this report, take a minute to read the text on-screen as part of the edit query form. It explains these tips and more.
Average of ratings: Useful (1)