General developer forum

Useful SQL Queries?

Picture of Ruthine Burton
Re: Useful SQL Queries?

I managed to write the query to find pdfs, but would welcome any comments on better ways of writing this. It may also be useful to anyone else who gets a request like this.

The code:


    prefix_files.filename AS PDF,

    prefix_course.fullname AS Course,

CONCAT('<a target="_new" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',,'">link</a>') AS 'Link To Course',

   prefix_course_modules.course     AS 'Course id'

FROM prefix_files

INNER JOIN prefix_context 

ON prefix_files.contextid =

INNER JOIN prefix_course_modules

ON prefix_context.instanceid =

INNER JOIN prefix_course

ON prefix_course_modules.course =

WHERE (prefix_files.filename LIKE '%pdf%')


Average of ratings: -
Picture of Eoin Campbell
Re: Useful SQL Queries?
Core developersParticularly helpful MoodlersPlugin developers

Just to note that you don't need to use CHAR(63) instead of the question mark '?' in your query. I noticed in a sample query in the Moodle documentation at Custom SQL queries report, it uses %%Q%% instead. I've tested and it works, so it makes code a little bit simpler. Instead of:

CONCAT('<a href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',,'">link</a>')

you can use:

CONCAT('<a href="%%WWWROOT%%/course/view.php%%Q%%id=',,'">link</a>')
Average of ratings: -