Useful SQL Queries for #Moodle

Some time ago, I had the opportunity to share some of the MySQL tips I had discovered in my work with MySQL, and Moodle, in particular. You can find some more neat stuff below....

  1. Useful SQL Queries - here is a list of the neat stuff you'll find there:
    • counts up the modules added to a given course
    • a basic report with a row per course, counting the amount of students, student course views (hits) and the average view per student
    • Select users who have not logged in for over 180 days
    • Delete users who have not logged in for over 180 days
    • Select users Who have NEVER logged in
    • Delete users Who have NEVER logged in
    • list number of views (hits) per student in a course from 2010. just change 1965 to your course id number.
    • count for resources and activities in a given course
    • lists all the resources and modules available and makes a count for a given course for those resources or modules that course contains.
    • see permissions overides on categories
    • show totally opened courses (visible, opened to guets, with no password)
    • find all information about instructors and the courses they are enrolled in
  2. Custom SQL Queries Report
Also check out this Custom SQL Queries administator tool and Configurable Reports. Do you know of other neat resources for Moodle?

Here's one contribution by Doris Johnson--with my modification in bold to get an alphabetical list--via the MoodleMayhem email list:

This query will make a list of all students that are not in a course: 
From phpmyadmin, I selected my database name. Then I selected the SQL tab. I copied and pasted the following query in the sql box. 
SELECT `u`.`id`, `u`.`username`, `u`.`firstname`, `u`.`lastname`, `u`.`email` 
FROM `mdl_user` `u` LEFT JOIN `mdl_role_assignments` `r` ON `r`.`userid` = `u`.`id` 
WHERE `r`.`id` IS NULL AND `u`.`deleted` = 0
ORDER BY `u`.`lastname`, `u`.`firstname`;

Here are the links to my Moodle Mayhem workshop stuff:

Overview of MySQL and Queries Possible
  1. Introduction to MySQL
  2. Creating MySQL Databases
    • General Settings for a new database:
      • UTF8
      • General_ci
    • How To Create Your Database with...
  3. Backing Up Your Moodle database using Free, Open Source Tools
  4. Restoring Your Moodle database
  5. Resetting Your Moodle Admin Password via the MySQL Database Interface
    • Ever find yourself unable to login with your admin password to Moodle? This may mean you need to repair a corrupt Moodle database (see below). It could also mean you forgot your password.
    • To change the admin password, go to a Moodle database for which you know the admin password. Copy the password (usually encrypted). Then, go to the mdl_user table, and find the admin user and replace the contents of the password field with your previously copied password.
  6. Other questions?

Miscellaneous Tips

Get Blog Updates via Email!
Enter your email address:
Delivered by FeedBurner

Everything posted on Miguel Guhlin's blogs/wikis are his personal opinion and do not necessarily represent the views of his employer(s) or its clients. Read Full Disclosure


Popular posts from this blog

COVID-19 Droplet Spread and #FREE Tests

Trying a New Pup Out #SPCA #Dog

AudibleNotes: Culturally Responsive Teaching for Multilingual Learners