How to: Ban Everyone is ICQ UIN

[14kvision]

Hi,

I am in the mists of cleaning up my board from thousands of spam accounts and messages. I think I've managed to clear out all the messages but now I'm left with a ton of spam accounts. I noticed that the majority of these spam accounts have an ICQ UIN in their profile and none of my legitimate users have an ICQ.

SO....

Is it possible to move any account which has an ICQ entry in their profile to the banned users group?

Thanks!

[TheLastSuperman]

You can run a SQL Query:

UPDATE user SET usergroupid = '8'
WHERE icq != '';

However that will change everyone who has anything entered into ICQ into the banned usergroup (defaulted to usergroup id #8) so be careful OR add in a AND as so:

UPDATE user SET usergroupid = '8'
WHERE icq != '' AND usergroupid != '6';

So now it should run through all of the users and place them into the banned group IF they are not in the admin usergroup.

When your done w/ the query you decide to go with don't forget to update your counters in admincp so their usertitles are also updated :cool:.

[14kvision]

Is that done somewhere in the cpanel? Or do I do that in PHP my admin?

[TheLastSuperman]

^ phpMyAdmin and check my post above once more I just finished editing ;).

[14kvision]

Seems to have worked like a charm! Thank you very much!!!

[TheLastSuperman]

Seems to have worked like a charm! Thank you very much!!!

Your welcome ;).

[Boofo]

You should be able to run the query in the Run Queries in the Admin CP, shouldn't you?

[TheLastSuperman]

You should be able to run the query in the Run Queries in the Admin CP, shouldn't you?

Should unless your not listed as having permission in the config file to run queries remember :cool:.

[djjeffa]

is there anyway to do this with members with 0 post and a icq username?

[djjeffa]

is there anyway to do this with members with 0 post and a icq username?

bump?

[Disasterpiece]

UPDATE user SET usergroupid = '8'
WHERE icq != '' AND usergroupid != '6' AND posts < 1;
too easy, eh? :P

[djjeffa]

UPDATE user SET usergroupid = '8'
WHERE icq != '' AND usergroupid != '6' AND posts < 1;
too easy, eh? :P

I tried and got this An error occurred while attempting to execute your query. The following information was returned.
error number: 1146
error desc: Table 'djjeffa_djjeffavb.user' doesn't exist

[djjeffa]

I tried and got this

bumP....

[Zachery]

Need to use your table prefix.

UPDATE PREFIXuser SET usergroupid = '8'
WHERE icq != '' AND usergroupid != '6' AND posts < 1;

[djjeffa]

Need to use your table prefix.

UPDATE PREFIXuser SET usergroupid = '8'
WHERE icq != '' AND usergroupid != '6' AND posts < 1;

wow I missed this lol.
hate to sound stupid but what is the table prefix?

[Zachery]

A prefix added on all of your tables specified in your config.php file that all of your database tables have

[djjeffa]

my prefix is vb but when I put
UPDATE vb/user SET usergroupid = '8'
WHERE icq != '' AND usergroupid != '6' AND posts < 1
I get
An error occurred while attempting to execute your query. The following information was returned.
error number: 1064
error desc: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/user SET usergroupid = '8'
WHERE icq != '' AND usergroupid != '6' AND posts < ' at line 1
im I doing it wrong?

[Zachery]

I very much doubt your table prefix is vb/

[djjeffa]

I very much doubt your table prefix is vb/

this is from my confige file am I doing somthing wrong?
// ****** TABLE PREFIX ******
// Prefix that your vBulletin tables have in the database.
$config['Database']['tableprefix'] = 'vb';

[Zachery]

UPDATE vbuser SET usergroupid = '8'
WHERE icq != '' AND usergroupid != '6' AND posts < 1;

[djjeffa]

UPDATE vbuser SET usergroupid = '8'
WHERE icq != '' AND usergroupid != '6' AND posts < 1;
im useless with this stuff but thanks for tring to help.
An error occurred while attempting to execute your query. The following information was returned.
error number: 1146
error desc: Table 'djjeffa_djjeffavb.vbuser' doesn't exist

[djjeffa]

im useless with this stuff but thanks for tring to help.
An error occurred while attempting to execute your query. The following information was returned.
error number: 1146
error desc: Table 'djjeffa_djjeffavb.vbuser' doesn't exist

any ideal?