How to Efficiently Delete Unenrolled Users in Tutor LMS Using SQL: A Step-by-Step Guide

When managing a growing WordPress site powered by Tutor LMS, it’s essential to maintain a clean database. Over time, your site can accumulate many registered users who aren’t enrolled in any course. These users can clutter your database, slow down your site, and increase maintenance complexity. In this detailed guide, we’ll walk you through filtering and deleting non-enrolled users who have roles like subscriber or customer using an SQL query.

Overview of the Problem

If you have over 6,000 registered users and only around 3,000 students, it’s important to manage the remaining users effectively. A large number of inactive users who aren’t enrolled in any courses can impact database performance, and keeping the data clean will improve both the speed and manageability of your WordPress site. Additionally, inactive or irrelevant users can inflate user counts, which affects reporting, email lists, and marketing strategies.

Tutor LMS stores enrollment data in the WordPress wp_posts table, using a custom post type called tutor_enrolled. This post type tracks course enrollments, linking the author_id field to the enrolled user's user_id. Meanwhile, WordPress itself stores user roles, such as subscriber and customer, in the wp_usermeta table.

The goal of this guide is to help you identify and delete registered users who:

1. Are not enrolled in any Tutor LMS course.

2. Have specific user roles, such as subscriber or customer.

Understanding the Database Tables

Before running the SQL query, it’s crucial to understand the main tables involved in this cleanup process:

1. `wp_users` Table

This table contains all registered users of your WordPress site. Key columns include:

- ID: The unique identifier for each user.

- user_login: The username used to log in.

- user_email: The email address of the user.

2. `wp_posts` Table

In WordPress, everything from posts to custom post types like tutor_enrolled is stored in the wp_posts table. In Tutor LMS:

- post_type: The type of post (in this case, tutor_enrolled for enrollments).

- post_author: This field stores the ID of the user who is associated with the enrollment (same as the ID in the wp_users table).

3. `wp_usermeta` Table

This table stores metadata for users, including their roles. It’s crucial for filtering users by role.

- meta_key: The key for the metadata, such as wp_capabilities, which stores the user’s role.

- meta_value: The actual value of the metadata, for example, the role subscriber or customer.

Steps for Deleting Non-Enrolled Users

Step 1: Backup Your Database

Before running any SQL queries that delete data, always back up your database. This step ensures that you can restore your site in case anything goes wrong. You can create backups using your web hosting control panel, a plugin like UpdraftPlus, or manually using phpMyAdmin.

Step 2: Understand the SQL Query

We’ll use a query that joins the wp_users table (which contains all registered users) with the wp_posts table (which stores Tutor LMS enrollments) and the wp_usermeta table (which holds user roles). This query identifies users who aren’t enrolled in any courses and filters them based on the subscriber or customer roles.

Step 3: SQL Query to Delete Non-Enrolled Users

Here is the SQL query that accomplishes this:

DELETE u

FROM wp_users u

LEFT JOIN wp_posts p ON u.ID = p.post_author AND p.post_type = 'tutor_enrolled'

LEFT JOIN wp_usermeta um ON u.ID = um.user_id

WHERE p.ID IS NULL

AND (

    um.meta_key = 'wp_capabilities'

    AND (um.meta_value LIKE '%subscriber%' OR um.meta_value LIKE '%customer%')

);        

Detailed Breakdown of the SQL Query

- `DELETE u`: This command specifies that we are deleting rows from the wp_users table (aliased as u).

- `LEFT JOIN wp_posts p`: This join connects the wp_users table (`u`) to the wp_posts table (`p`). It ensures that we include all users, even if they don’t have any posts (enrollments). The condition p.post_type = 'tutor_enrolled' restricts the query to course enrollment posts.

- `LEFT JOIN wp_usermeta um`: This join connects the wp_users table to the wp_usermeta table (`um`) to retrieve user roles. The condition um.meta_key = 'wp_capabilities' ensures that we are fetching the user role metadata.

- `WHERE p.ID IS NULL`: This condition ensures that we only target users who have no corresponding tutor_enrolled posts in the wp_posts table, meaning they aren’t enrolled in any courses.

- `AND (um.meta_value LIKE '%subscriber%' OR um.meta_value LIKE '%customer%')`: This condition filters users who have a subscriber or customer role.

Step 4: Run the Query

Once you’ve backed up your database, you can execute the SQL query. You can do this via:

- phpMyAdmin:

1. Log in to phpMyAdmin from your web hosting control panel.

2. Select your WordPress database.

3. Click the SQL tab.

4. Paste the SQL query and click Go.

- Command Line (SSH Access):

1. Log in to your server via SSH.

2. Use a MySQL command line client to access your WordPress database.

3. Paste the query and execute it.

Step 5: Verify the Results

After running the query, it’s important to verify that the correct users have been deleted:

- Check your wp_users table to see the remaining users.

- Log in to your WordPress admin panel and ensure that no legitimate users or students were mistakenly removed.

Handling Edge Cases

- Multiple Roles: If users have multiple roles (e.g., subscriber and editor), you may want to adjust the query to account for this, ensuring only users who exclusively have the subscriber or customer role are deleted.

- Custom Roles: If your site uses custom roles beyond subscriber and customer, modify the query to include them, if necessary.

Benefits of Cleaning Up Non-Enrolled Users

1. Improved Database Performance

A bloated database with many unnecessary users can slow down your WordPress queries and impact performance, especially if your site relies on user-specific data. Deleting non-enrolled users reduces the load on the database and can speed up site operations.

2. Easier User Management

With fewer inactive users, managing your WordPress users becomes simpler. This also improves the accuracy of reports, user lists, and marketing efforts, as you’ll be working with a more relevant audience.

3. Cost Savings

If your hosting provider charges based on database size or storage, reducing the number of inactive users can lead to cost savings. It can also help with email marketing tools, which often charge based on the size of your subscriber list.

4. Enhanced Security

Having inactive or unused accounts on your website can pose a security risk. Cleaning up non-enrolled users reduces the number of potential targets for malicious activity, making your site more secure.

Conclusion

Cleaning up non-enrolled users in Tutor LMS using the SQL query provided helps maintain a lean, efficient WordPress database. By removing users who aren’t engaged in any courses and who have specific roles like subscriber or customer, you can enhance site performance, improve user management, and save on storage costs. Just remember to back up your database before making any major changes!

If you need more help customizing the query or understanding other aspects of database management, feel free to reach out!