Mass delete members in ExpressionEngine
Posted on September 10, 2013
I read over EE forums like ExpressionEngine Community Support and ExpressionEngine StackExchange, some people asking to delete 1000+ members. Which is much time consuming if deleting all those members from Control Panel.
Here, I would like to share an easy way to delete mass members with single SQL query.
Basically member’s data are being stored into database tables exp_members, exp_member_data, and exp_member_homepage. So there will need to delete the member’s records from these 3 tables at a time. If you see the tables structure, having column member_id which relates these 3 tables.
Below is SQL for deleting member’s records from these 3 tables (exp_members, exp_member_data, and exp_member_homepage) and also from ExpressionEngine install.
DELETE exp_members, exp_member_data, exp_member_homepage FROM exp_members INNER JOIN exp_member_data INNER JOIN exp_member_homepage WHERE exp_member_data.member_id = exp_members.member_id AND exp_members.member_id = exp_member_homepage.member_id AND exp_members.group_id = 'x'
In the above SQL query ‘x’ is the member group id of which all the members will get deleted.
Also, all the members with no entry i.e entry can be deleted:
DELETE exp_members, exp_member_data, exp_member_homepage FROM exp_members INNER JOIN exp_member_data INNER JOIN exp_member_homepage WHERE exp_member_data.member_id = exp_members.member_id AND exp_members.member_id = exp_member_homepage.member_id AND exp_members.total_entries = 0
I have tested this SQL query and worked fine but I would suggest you to take backup of database to avoid any accident.
The above approach and SQL query will not work if you would like delete entries authored by those members. Yes, members can be deleted from Control Panel but only 20 members at a time. To delete 1000 members, you have to follow same step 50 times.
Lets do a trick.
Login to EE Control Panel -> Members -> View All.
Now you can see the page URL like ….. C=members&M=view_all_members. Add query string group_id=a&perpage=bbbb where “a” can be any member group id and “bbbb” can be number of members (500, 1000 …. ) to be deleted at once. So make the page URL like: ….. C=members&M=view_all_members&group_id=X&perpage=Y where X might be member group id and Y might be number like 500, 1000…. Check all the checkboxes with single click and delete by clicking button “Delete Selected Members”. You have done with one more step . 🙂