Newgrounds.com — Everything, By Everyone.

Ok, so at one point or another, I'm probably gonna need to delete a user from my system. My initial idea was to simply do

DELETE FROM users WHERE id='123'

and before deletion, add their email address to a banned emails table. However, this will cause some problems when I try to pull posts out of the database that belong to that user. I have two options-

1. delete all posts/topics by that user and then delete them

or

delete everything in their user record except for id and email. That way when their id is pulled out and a blank username follows, I can choose to insert <deleted> for their name. Plus their email will remain in the users table, making a 'banned emails' table obsolete.

Or maybe I can just flat out delete them and user some kind of outer join to still bring back blank records for missing users.

Suggestions?

At 10/30/05 01:24 AM, Afro_Ninja wrote: Suggestions?

Just flat out delete them, then when you pull the post or thread with their ID as author, check to see if a user with that id exists in the user table (which you should be doing anyway), if it doesn't username=<deleted>.

At 10/30/05 01:36 AM, White_Rhyno wrote:

At 10/30/05 01:24 AM, Afro_Ninja wrote: Suggestions?

Just flat out delete them, then when you pull the post or thread with their ID as author, check to see if a user with that id exists in the user table (which you should be doing anyway), if it doesn't username=<deleted>.

Seems over the top to do a database check for the author id on each single post pulled out. Especially in the small chance that one of them doesn't exist.

At 10/30/05 01:40 AM, Afro_Ninja wrote: Seems over the top to do a database check for the author id on each single post pulled out. Especially in the small chance that one of them doesn't exist.

Yes, this is a reasonable problem. Unless you're having to delete a lot of users because you have a lot breaking the rules, it wouldn't be a bad idea to just update their record as <deleted>. However, like NG, if you have a lot of users who need to be dealt with, the other method could prevail.

As part of the delete user query, just do
update posts where author = author to be deleted (author) <delortedz>

So it replaces all posts by the author to be deleted with deletedz

At 10/30/05 01:40 AM, Afro_Ninja wrote: Seems over the top to do a database check for the author id on each single post pulled out. Especially in the small chance that one of them doesn't exist.

I guess it all depends how you set out your posts table. When I was messing around with a custom bbs a few moons ago, I set it out so that the posts table didn't contain the users name, instead the user id, which it then referenced to the user table to get the user name.

Now that I think about it, that's not very good database design, so it's a good thing I'm taking the Database Design and Implementation class next semester.

Users realy don't need to be deleted in the first place, just set a ban flag or change the user group to banned.

At 10/30/05 03:49 AM, henke37 wrote: Users realy don't need to be deleted in the first place, just set a ban flag or change the user group to banned.

I think you're talking in phpBB. Afro_Ninja coded his own.

So it replaces all posts by the author to be deleted with deletedz

hmm, yeah, that's an option too

Now that I think about it, that's not very good database design, so it's a good thing I'm taking the Database Design and Implementation class next semester.

That is how I have it set up... the posts table has an 'author' field which is more or less a foreign key to the user id. How else would you set it up? A post has to be linked to an author in some way

Users realy don't need to be deleted in the first place, just set a ban flag or change the user group to banned.

You're right, I can't see myself having to delete a lot of users, but just in case the need arises...

I think you're talking in phpBB. Afro_Ninja coded his own.

aye

I have an idea.

First, when you delete a user, set their username as "<deleted>"

THEN set a cron jub to run once every week.. This cron job will execute a PHP script that goes through the users table finds all the usernames that == '<deleted>' and deletes all their posts. SO this way the cron job could run it at an off time when not many people are on.

Personally, when doing the delete script I would have it do these things;

- Delete everything in the person's profile.
- Keep forum posts+threads
- Re-name thier name to <deleted>
- Put e-mail+I.P into a seperate table, so you can see whos been deleted.

Then, when it comes to form posts, you could do like..

if username = <deleted>, show no stats.

Like on NG.

Then, if that I.P in the table trys and signs up, you could put: You have had an account deleted, and its not appreciated if you sign-up again.

Then on thier new profile put a message saying.. "I've had an account deleted on this I.P. So watch out."

Or something, so you know. Lol.

Confusing, but thats what I would do.

I don't forsee having to delete a lot of users so I think I'll just delete all their posts when I delete them.

I wouldn't delete the posts. Infact, i wouldnt delete the user. Just leave all the necessities, and delete personal /public profile stuff. But deleting a user will just lead to seeong a bunch of <deleted>s' all over the place. and if you try to get arround that by deleting the users post, it can end up making allot of threads usless.

At 11/1/05 11:58 PM, BulletProof57 wrote: I wouldn't delete the posts. Infact, i wouldnt delete the user. Just leave all the necessities, and delete personal /public profile stuff. But deleting a user will just lead to seeong a bunch of <deleted>s' all over the place. and if you try to get arround that by deleting the users post, it can end up making allot of threads usless.

So what you are saying is that each user has there own table that records there posts in the database? Sorry for just budding in and saying something that's sort of irrelivant but I'm just observing conversations on the programming board to see if I can learn something.

At 11/2/05 12:24 AM, Greeley wrote: So what you are saying is that each user has there own table that records there posts in the database? Sorry for just budding in and saying something that's sort of irrelivant but I'm just observing conversations on the programming board to see if I can learn something.

No, it's two separate tables. One for users, one for posts. The user table of course, has an id field that uniquely identifies that user. A record in the posts table has several values, one of which being author. Author is recorded as that users id. That's what links the posts and users together

so when user id 4 makes a post, it goes something like

INSERT INTO posts (author,body,date) VALUES (4,'blah',NOW())

I was way off! :P
But thankyou for helping me to understand this a little better Afro Ninja . . .
I'm working at understanding PHP to become at least half descent. I'd love to be at yours or Doofy's skill level when it comes to PHP. But what you have just told me makes alot more sence than what I had said! :P

do you not have PhpMySql database access??

At 11/2/05 01:02 PM, -BuRnBoY- wrote: do you not have PhpMySql database access??

did you not read the thread??

This issue is not just deleting users, it's deleting users and then taking care of records that are linked to that user, such as bbs posts and topics

If you're not going to delete many people, then delete their posts as well. After all, you deleted them for a reason so why leave their offinsive posts on the forum for all to see?

Actually, i wouldn't delete anything. I would just move them and all of their information to a another table for later reffrence. That way, if needs be, you could alwas recreate the posts and users in question by loading the topic from both tables. That, and it's always good to have a record of who was deleted, when, why, and what they had done while they where on your site.