- 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:
- 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?
Enter your email address:
Delivered by FeedBurner