It’s happened to a lot of us at one time or another: we’ve deleted a member from our Expression Engine 1.6.9 site and then said—Oops! That wasn’t a spammer! That was our most important forum volunteer! (OK, maybe not to ALL of us, but to some of us, certainly.)
How do you get that member back? Well, there are a lot of places within the EE codebase where members can be deleted, and each one is coded slightly differently. They can be deleted from the control panel by an admin individually or as a batch, they can be deleted from the control panel (or from the public-facing pages) as a self-deletion under the My Account tab, and they can be deleted from a link on any forum post they make, by an admin. In each case, once you delete them, you will likely end up with all the member’s content and settings deleted—though in some cases, their weblog entries are migrated to someone else, and in some other cases, reply posts to a member’s forum topics don’t seem to be deleted (though they probably ought to be).
In a nutshell, to restore a member deleted accidentally, you’re going to have to:
a) find and access an earlier version of the EE database where that member’s account and data still exists
b) select all the relevant data from that database, and then
c) insert it back into the current database.
You’ll need the data from the following queries:
SELECT * FROM exp_members WHERE member_id = #deleted#;
SELECT * FROM exp_member_data WHERE member_id = #deleted#;
SELECT * FROM exp_member_homepage WHERE member_id = #deleted#;
SELECT * FROM exp_message_copies WHERE sender_id = #deleted#;
SELECT * FROM exp_message_data WHERE sender_id = #deleted#;
SELECT * FROM exp_message_folders WHERE member_id = #deleted#;
SELECT * FROM exp_message_listed WHERE member_id = #deleted#;
SELECT * FROM exp_forum_subscriptions WHERE member_id = #deleted#;
SELECT * FROM exp_forum_pollvotes WHERE member_id = #deleted#;
SELECT * FROM exp_forum_polls WHERE author_id = #deleted#;
SELECT * FROM exp_forum_topics WHERE author_id = #deleted#;
SELECT * from exp_forum_posts WHERE topic_id IN (SELECT topic_id FROM exp_forum_topics WHERE author_id = #deleted#) OR author_id = #deleted#;
/* NOTE: This selects the author’s own posts and the posts that were deleted when the member’s topics were deleted. */
SELECT * FROM exp_forum_attachments WHERE member_id = #deleted#;
/* NOTE: Deleting a member deletes their file attachments—restoring those is beyond being a database issue, and restoring the attachment entries doesn’t re-create the files. */
SELECT * FROM exp_forum_administrators WHERE admin_member_id = #deleted#;
SELECT * FROM exp_forum_moderators WHERE mod_member_id = #deleted#;
SELECT * FROM exp_weblog_titles WHERE author_id = #deleted#;
/* NOTE: If there are any, you have an even bigger restore issue, because you need to restore exp_weblog_data and exp_weblog_comments as well, which the next two lines find */
SELECT * FROM exp_weblog_data WHERE entry_id IN (SELECT entry_id FROM exp_weblog_titles WHERE author_id = #deleted#;)
SELECT * FROM exp_comments WHERE entry_id IN (SELECT entry_id FROM exp_weblog_titles WHERE author_id = #deleted#;)
/* NOTE: I left out exp_gallery_entries because I don’t use it. If you do, don’t overlook it. */
SELECT * FROM exp_comments WHERE author_id = #deleted#;
At this point, with all these selects, you should have all the data you need to reinsert, not including any third-party modules which likely didn’t have their data erased—but you never know, so it’s worth checking third-party tables, too.
Side note: if you write a module that stores data tied to user accounts, you should likely deal with that data when the member delete is called—there’s an extension hook you can watch.
Make lots of backups, reinsert, and hope you never have to do this again.
Oh, and lastly, go here: CP Home ? Admin ? Utilities ? Recount Statistics
Final thought: If you need a member restored, give us a shout, we’ll be glad to help.
Comments