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....
- 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
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` = 0ORDER BY `u`.`lastname`, `u`.`firstname`;
Here are the links to my Moodle Mayhem workshop stuff:
Overview of MySQL and Queries Possible
- Introduction to MySQL
- Creating MySQL Databases
- General Settings for a new database:
- How To Create Your Database with...
- Backing Up Your Moodle database using Free, Open Source Tools
- Backing up with phpMyAdmin (web-based)
- Backing up with SQL Yog (Windows only)
- Restoring Your Moodle database
- 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.
- Other questions?
Other Fun Stuff
Get Blog Updates via Email!
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