Sending output to CSV
You can send your results to csv format by adding the following line after the SELECT statement.
INTO OUTFILE '/var/lib/mysql-files/output.csv'
FIELDS TERMINATED BY ',' ESCAPED BY '"'
ENCLOSED BY '"'
LINES TERMINATED BY 'rn'
Example
SELECT id, firstname, lastname, email from customers
INTO OUTFILE '/tmp/output.csv'
FIELDS TERMINATED BY ',' ESCAPED BY '"'
ENCLOSED BY '"'
LINES TERMINATED BY 'rn'
WHERE firstname='fred'
List all courses using Turnitintwo module
Replace <mydomain> with your domain fqdn
SQL
SELECT an.name, CONCAT('https://mydomain/course/view.php?id=',co.id) as url , co.shortname, "Turnitin" as type, from_unixtime(an.defaultdtdue) as duedate
FROM mdl_turnitintooltwo an join mdl_course co on an.course = co.id
ORDER BY duedate;
List all courses
- Include the url to the course
- By last access time
Replace <mydomain> with your domain fqdn
SQL
select y.*
from
(
select x.id, x.fullname, x.shortname, x.name as category, FROM_UNIXTIME(x.timeaccess) last_accessed_time, CONCAT('https://mydomain/course/view.php?id=',x.id) as course
from
(
select c.id, c.fullname, c.shortname, cc.name, max(a.timeaccess ) timeaccess
from mdl_course c
left join mdl_course_categories cc
on cc.id = c.category
left join mdl_user_lastaccess a
on c.id = a.courseid
group by c.id, c.fullname, c.shortname, cc.name
) x
) y
order by last_accessed_time asc;
Lowercase Username and Email
Change all usernames and email addresses to lowercase
SQL
UPDATE mdl_user SET username = LOWER(username);
UPDATE mdl_user SET email = LOWER(email);
--