How to extract queries?

[G0erman]

I would like to know how to extract the SQL queries to execute it directly in MySQL database, I don't have PHP skills; Could you give me some advice on how to do it?

For a previous post, I know that the number of resources accessed by week is:

set @start_date = 1479313275; -- someday in unix time format
set @one_day = 86400; -- Seconds in one day
set @ajust = 0; -- I'm not sure what did this?

SELECT 
    userid,
    LOWER(firstname),
    LOWER(lastname),
    email,
    week,
    number
FROM
    (SELECT 
        temp.userid, temp.week, COUNT(*) AS number
    FROM
        (SELECT 
        log.userid,
            log.objecttable,
            log.objectid,
            FLOOR((((log.timecreated + @ajust) / @one_day) - (@start_date / @one_day)) / 7) AS week
    FROM
        mdl_logstore_standard_log AS log
    WHERE
        1 AND log.courseid = 99 -- to set.
            AND log.action = 'viewed'
            AND log.target = 'course_module'
            AND log.userid >= 0
    GROUP BY log.userid , log.objecttable , log.objectid , week) AS temp
    GROUP BY temp.userid , temp.week) AS temp_2
        LEFT JOIN
    mdl_user AS user ON temp_2.userid = user.id
ORDER BY LOWER(firstname) , LOWER(lastname) , userid , week
;

I try to get "number of days by week with access", following the same logic.

SELECT 
    userid,
    LOWER(firstname) AS first_name,
    LOWER(lastname) AS last_name,
    email,
    week,
    number
FROM
    (SELECT 
        temp.userid, temp.week, COUNT(*) AS number
    FROM
        (SELECT DISTINCT
        log.userid,
            DATE_FORMAT(FROM_UNIXTIME(timecreated), '%Y-%m-%d') AS day_created,
            FLOOR((((log.timecreated + @ajust) / @one_day) - (@start_date / @one_day)) / 7) AS week
    FROM
        mdl_logstore_standard_log AS log
    WHERE
        1 AND log.courseid = 99
            AND log.eventname LIKE '%course_viewed%' -- '\core\event\course_viewed'  
            AND log.userid >= 0
    GROUP BY log.userid , day_created , week) AS temp
    GROUP BY temp.userid , temp.week) AS temp_2
        LEFT JOIN
    mdl_user AS user ON temp_2.userid = user.id
ORDER BY LOWER(firstname) , LOWER(lastname) , userid , week
;

Now I want to get the "Course hits" to complete the same graphs of the image.

Thank you.

[marceloschmitt]

Let me see if I can help you. You want to see the SQL query. The best way is to enable MySQl log (or whatever DB you are using). And copy the query that was executed.

[G0erman]

Thanks, I don't have administrator rights to enable MySQL log in the system (To do: create my test server), I did it searching in the class lib.php and transforming the queries with this function.