1

Topic: Fixing member id's in Pun Database [paid gig]

I have a several members which have been deleted, whose posts still show up as "Guest" in our forum.

What I want to do is convert those deleted members posts ID's into a real account so I can deal with them separately.

Willing to pay!

If interested, please email me off-forum.

TIA

Re: Fixing member id's in Pun Database [paid gig]

Not sure if this will work (have no "orphaned" posts in my db)...

To find posts where users don't exist:

SELECT p.id
FROM posts p
LEFT JOIN users u
ON p.poster_id = u.id
WHERE u.id IS NULL

Then set poster_id for these rows to some new user id, etc etc?

Re: Fixing member id's in Pun Database [paid gig]

Well, the simplicity depends on two things
1. Do you have any other Guest posts?
2. Do you want these all on one account, or one for each member?

If the answer to 1 is No, and the answer to 2 is one account, then you've made it very simple. Just create a new user, get the user id, and update poster_id to the new user where it's currently 1.
If the answer to 1 is yes and the answer to 2 is one account, then you do something similar, but use the poster column in the posts table instead
If the answer to 1 is no, and the answer to 2 is multiple accounts, then you do just like you would above, except you only assign one poster to one account.
If the answer to 1 is yes, and the answer to 2 is multiple accounts, then it's the same as the two above (not the first one) pretty much.

Just tell me what your situation is and I'll write out a query tongue

Re: Fixing member id's in Pun Database [paid gig]

Is poster_id set to 1 for each related post when (before?) a user is deleted?

What about an "unmanaged" delete, i.e. if I delete a user through (for example) phpmyadmin? In that case I guess the "left join ... is null" query above will be useful?

Re: Fixing member id's in Pun Database [paid gig]

Is poster_id set to 1 for each related post when (before?) a user is deleted?

Assuming the box to delete posts/threads isn't checked

What about an "unmanaged" delete, i.e. if I delete a user through (for example) phpmyadmin? In that case I guess the "left join ... is null" query above will be useful?

In an "unmanaged" delete, the poster_id would stay as it was. So yeah, your query might be useful there. However, you can also just tie that into an update statement (assuming that you want all guest posts to be on one account)

Re: Fixing member id's in Pun Database [paid gig]

Ray: I've already described to you how to fix this. Did my instructions not help?

"Programming is like sex: one mistake and you have to support it for the rest of your life."

7

Re: Fixing member id's in Pun Database [paid gig]

Rickard wrote:

Ray: I've already described to you how to fix this. Did my instructions not help?

neutral