I just made a new admin report

Re: I just made a new admin report

by David Richter -
Number of replies: 7

Thanks to all for the great SQL contributions, they are really helpful to someone who is not that strong in SQL (like me)!

I am running Moodle 2.2.1 and have installed the ad-hoc database query plugin. I was looking over the contributed SQL queries and found one that I was particularly interested in:

How many LOGINs per user and user's Activity

+ link username to a user activity graph report

SELECT concat('<a target="_new" href="%%WWWROOT%%/course/user.php?id=1&user=',u.id,'&mode=alllogs">',u.firstname ,' ',u.lastname,'</a>') AS Username
,count(*) AS logins
,(SELECT count(*) FROM prefix_log WHERE userid = l.userid GROUP BY userid) AS Activity
FROM prefix_log AS l JOIN prefix_user AS u ON l.userid = u.id
WHERE `action` LIKE '%login%' GROUP BY userid
ORDER BY Activity DESC

When I run the query, I receive the error:

ERROR: Incorrect number of query parameters. Expected 1, got 0.

Does someone know the source of the error?

Thanks in advance!

Average of ratings: -
In reply to David Richter

Re: I just made a new admin report

by Peter Roberts -

I've just started using the custom sql plugin and it's really handy. Thank you Tim and to all who have posted stuff here.

I have hit a slight snag, can anyone shed any light on using tokens in the queries?

I can use the USERID token fine - SELECT  %%USERID%% as uid for example, but I can't get the WWWROOT token to work at all. For example - SELECT %%WWWROOT%% AS w - I just get error messages.

I wanted to use it with CONCAT to create a hyperlink in the results as per the examples. I'm on Moodle 2.2.2 and slightly baffled!

Any suggestions welcome.

Average of ratings: -
In reply to Peter Roberts

Re: I just made a new admin report

by Peter Roberts -

I've just noticed another oddity - I can't include a ? in string concats - thus making it unlikely I can create a useful hyperlink in the results.

So, for example

SELECT concat('I can do these ','<>/\=! ') as ans

will work - but if I add a question mark in there I get an error - Error when executing the query: ERROR: Incorrect number of query parameters. Expected 1, got 0.

Average of ratings: -
In reply to Peter Roberts

Re: I just made a new admin report

by Steve Bond -

Hi Peter,

Thanks for this, I was wondering why my reports had all stopped working.

I managed to get around this by replacing any '?' in my query by CHAR(63)


Hope that helps someone

Steve

Average of ratings: Useful (3)
In reply to Steve Bond

Re: I just made a new admin report

by Susan Mangan -

Brilliant, thank you!!!

Average of ratings: -
In reply to Susan Mangan

Re: I just made a new admin report

by roc mehra -

Hi All,

I want to generate report of total hits per course from last one month.This query gives me total hits since course generated. i want to generate hits by time.
Thanks in advance.

SELECT COUNT(l.id) hits, l.course courseId, c.fullname coursename
FROM mdl_log l INNER JOIN mdl_course c ON l.course = c.id
GROUP BY courseId
ORDER BY hits DESC

Average of ratings: -
In reply to Steve Bond

Re: I just made a new admin report

by Italo Marques -

I know this is a very old thread but isn't there's no workaround about this?

Isn't there any configuration setting that will allow having a '?' in concatenated string on the query? It's a little bit annoying to replace any ? with CHAR(63)

But anyways...thanks for this. It was driving me mad.

Average of ratings: -