Pligg: Spam Votes und User entfernen aus MySQL

Link:

Killspamming and disabling them is fine but the votes made by the disabled user still seem to exist. Not sure if this is an issue but disabled users still appear on the who voted sections on stories.

Anyway, to delete users and their votes once you’ve disabled and killspammed them use this query in PHPMyAdmin:

SELECT * FROM `bs_votes` WHERE `vote_user_id` IN (SELECT `user_id` FROM `bs_users` WHERE `user_email` = ‘blank@blank.com’);

This will select all the votes from users who have an email address set as blank@blank.com. Now all you have to do is select „Check All“ and click the delete icon and everything that was left of the votes after the killspam should be gone.

Now to delete all the users, now that their votes have gone, just use:

SELECT * FROM `bs_users` WHERE `user_email` = ‘blank@blank.com’

and click „Check All“ and delete!

Only issue with this though is that the stories themselves have a number associated with them saying how many votes they have!

The spammy user data is gone and so is the related vote data for them in the vote table, reducing the amount of redundant data.

The tip above will help reduce the size of your database but each story will still say the amount of votes its been given.

Leaving the amount of votes each story has will not unecessarily increase the database size, so this isnt an issue technically wise. Unless you REALLY dont’ want have the votes from deleted users there.

Now from looking into it for a bit I couldnt see any easy way of correcting the figures for the story votes as they seem to be incremented and not relational to the vote tables, so I couldnt create a MYSQL query regarding this.