Smf Forum – Merge Member Posts and PM’s with MySQL

So what happens when you decide to have a clean up on your Simple Machines Forum, and part of that clean up is to remove inactive users?

Inactive Members Removed
Well, the user gets removed but their posts remain on the forum unless you specifically delete the account and choose to remove all post. Otherwise the username remains with the word “Guest” alongside each post.

Member Returns
It can sometimes happen that a member who hasn’t been around for a while comes back and has to create a new account as their old account has been removed. This leaves them with a zero post-count and all their PM’s associated with the old account.

How Do I Re-associate All Old Member Data with New Account?
Pretty easily really, with access to the database via phpMyAdmin and a few MySQL Query commands. On every Database, there is a Tab at the top which says “SQL” and has a box where you can issue queries (commands) and hit “Go”.
*Warning* BACKUP YOUR DATABASE FIRST !!

The main Syntax that we are going to issue as a MySQL query is this:
UPDATE whatever_table SET ID_MEMBER_whatever = '2', whateverName = 'new' WHERE whateverName = 'old'

1. Associating Old Posts with new Member account
For example, we want to change all forum posts with the name of a deleted member called OldBuddy, and associate them with his new name which is NewBuddy with member ID 1337.
UPDATE smf_messages SET ID_MEMBER = '1337', posterName = 'NewBuddy' WHERE posterName = 'OldBuddy'

2. Now we want to do the same with his old PM’s (Private/Personal Messages):
UPDATE smf_personal_messages SET ID_MEMBER_FROM = '1337', fromName = 'NewBuddy' WHERE fromName = 'OldBuddy'

By now you should be getting the idea. You just UPDATE whatever table, with new column data for a user ID by changing old for new. Once you get the hang of it, you can issue the UPDATE command on any MySQL database, not just Smf Forum, and recursively replace any data in any table.