Updating Forum & User Options en masse (now for vB3!)

[tubedogg]

Remember you can now run queries from the vB3 Admin CP under Import & Maintenance.

Each of these queries will change the specified option for every user already registered.

"Show Signatures"
On: UPDATE user SET options=options + 1 WHERE NOT(options & 1)
Off: UPDATE user SET options=options - 1 WHERE options & 1
"Show Avatars"
On: UPDATE user SET options=options + 2 WHERE NOT(options & 2)
Off: UPDATE user SET options=options - 2 WHERE options & 2

"Show Images"
On: UPDATE user SET options=options + 4 WHERE NOT(options & 4)
Off: UPDATE user SET options=options - 4 WHERE options & 4

"COPPA User"
Yes: UPDATE user SET options=options + 8 WHERE NOT(options & 8)
No: UPDATE user SET options=options - 8 WHERE options & 8

Receive Admin Emails
On: UPDATE user SET options=options + 16 WHERE NOT(options & 16)
Off: UPDATE user SET options=options - 16 WHERE options & 16

vCard Download
On: UPDATE user SET options=options + 32 WHERE NOT(options & 32)
Off: UPDATE user SET options=options - 32 WHERE options & 32

DST Auto-Correct
On: UPDATE user SET options=options + 64 WHERE NOT(options & 64)
Off: UPDATE user SET options=options - 64 WHERE options & 64

DST On/Off
On: UPDATE user SET options=options + 128 WHERE NOT(options & 128)
Off: UPDATE user SET options=options - 128 WHERE options & 128

Show Email Address
On: UPDATE user SET options=options + 256 WHERE NOT(options & 256)
Off: UPDATE user SET options=options - 256 WHERE options & 256

Invisible
On: UPDATE user SET options=options + 512 WHERE NOT(options & 512)
Off: UPDATE user SET options=options - 512 WHERE options & 512

Show Reputation
On: UPDATE user SET options=options + 1024 WHERE NOT(options & 1024)
Off: UPDATE user SET options=options - 1024 WHERE options & 1024

Receive PMs
On: UPDATE user SET options=options + 2048 WHERE NOT(options & 2048)
Off: UPDATE user SET options=options - 2048 WHERE options & 2048

Get email for new PMs
On: UPDATE user SET options=options + 4096 WHERE NOT(options & 4096)
Off: UPDATE user SET options=options - 4096 WHERE options & 4096

Post Order
Newest first: UPDATE user SET options=options + 32768 WHERE NOT(options & 32768)
Oldest first: UPDATE user SET options=options - 32768 WHERE options & 32768

[tubedogg]

Additional queries that aren't covered by the options bitfield:

Threaded Mode:
On: UPDATE user SET threadedmode=1
Off: UPDATE user SET threadedmode=0

Popup on new PM:
On: UPDATE user SET pmpopup=1
Off: UPDATE user SET pmpopup=0

Default daysprune setting:
UPDATE user SET daysprune=X
where X is the number of days.

Language:
UPDATE user SET languageid=X
where X is the ID of the language (you can get this from the admin CP).

First day of the week:
UPDATE user SET startofweek=X
where X is a number 1 through 7 indicating the first day of the week (with 1 being Sunday, 2 being Monday, etc.).

Editor style:
None: UPDATE user SET showvbcode=0;
vB2.x-style toolbar: UPDATE user SET showvbcode=1;
WYSIWYG editor: UPDATE user SET showvbcode=2;

Thread subscription/email notification defaults:
Do not subscribe: UPDATE user SET autosubscribe=-1;
Subscribe without notification: UPDATE user SET autosubscribe=0;
Instant email: UPDATE user SET autosubscribe=1;
Instant ICQ: UPDATE user SET autosubscribe=4 WHERE icq <> ''; (note: This requires a valid ICQ number in the user's profile to work; also the user must have ICQ Email Express enabled in ICQ.)
Daily email: UPDATE user SET autosubscribe=2;
Weekly email: UPDATE user SET autosubscribe=3;

[tubedogg]

Updating Forum Options en masse

Remember you can now run queries from the vB3 Admin CP under Import & Maintenance. (See http://www.vbulletin.com/forum/showthread.php?t=88733 .)

Each of these queries will change the specified option for every forum that exists on your board.

"Forum is Active"
Yes: UPDATE forum SET options = options + 1 WHERE NOT(options & 1);
No: UPDATE forum SET options = options - 1 WHERE options & 1;

"Forum is Open"
Yes: UPDATE forum SET options = options + 2 WHERE NOT(options & 2);
No: UPDATE forum SET options = options - 2 WHERE options & 2;

"Act as Forum"
Yes: UPDATE forum SET options = options + 4 WHERE NOT(options & 4);
No: UPDATE forum SET options = options - 4 WHERE options & 4;

"Moderate Posts"
Yes: UPDATE forum SET options = options + 8 WHERE NOT(options & 8);
No: UPDATE forum SET options = options - 8 WHERE options & 8;

"Moderate Threads"
Yes: UPDATE forum SET options = options + 16 WHERE NOT(options & 16);
No: UPDATE forum SET options = options - 16 WHERE options & 16;

"Moderate Attachments"
Yes: UPDATE forum SET options = options + 32 WHERE NOT(options & 32);
No: UPDATE forum SET options = options - 32 WHERE options & 32;

"Allow BB Code"
Yes: UPDATE forum SET options = options + 64 WHERE NOT(options & 64);
No: UPDATE forum SET options = options - 64 WHERE options & 64;

"Allow IMG Code"
Yes: UPDATE forum SET options = options + 128 WHERE NOT(options & 128);
No: UPDATE forum SET options = options - 128 WHERE options & 128;

"Allow HTML"
Yes: UPDATE forum SET options = options + 256 WHERE NOT(options & 256);
No: UPDATE forum SET options = options - 256 WHERE options & 256;

"Allow Smilies"
Yes: UPDATE forum SET options = options + 512 WHERE NOT(options & 512);
No: UPDATE forum SET options = options - 512 WHERE options & 512;

"Allow Post Icons"
Yes: UPDATE forum SET options = options + 1024 WHERE NOT(options & 1024);
No: UPDATE forum SET options = options - 1024 WHERE options & 1024;

"Allow thread ratings"
Yes: UPDATE forum SET options = options + 2048 WHERE NOT(options & 2048);
No: UPDATE forum SET options = options - 2048 WHERE options & 2048;

"Count Posts Made in this Forum Towards User Post Counts"
Yes: UPDATE forum SET options = options + 4096 WHERE NOT(options & 4096);
No: UPDATE forum SET options = options - 4096 WHERE options & 4096;

"Can Have Password"
Yes: UPDATE forum SET options = options + 8192 WHERE NOT(options & 8192);
No: UPDATE forum SET options = options - 8192 WHERE options & 8192;

"Index New Posts in Search Engine"
Yes: UPDATE forum SET options = options + 16384 WHERE NOT(options & 16384);
No: UPDATE forum SET options = options - 16384 WHERE options & 16384;

"Override Users' Style Choice" (Only applies if you have selected custom style for each forum)
Yes: UPDATE forum SET options = options + 32768 WHERE NOT(options & 32768);
No: UPDATE forum SET options = options - 32768 WHERE options & 32768;

"Show this Forum and Child Forums on the Forum Jump Menu"
Yes: UPDATE forum SET options = options + 65536 WHERE NOT(options & 65536);
No: UPDATE forum SET options = options - 65536 WHERE options & 65536;

"Warn Administrators"
Yes: UPDATE forum SET options = options + 131072 WHERE NOT(options & 131072);
No: UPDATE forum SET options = options - 131072 WHERE options & 131072;



"Custom Style for this Forum":
UPDATE forum SET styleid = X;
where X is the styleid.

"Default View Age":
UPDATE forum SET daysprune = X;
where X is the number of days. (Use any positive number, or -1 for 'all'.)

"Email Addresses to Notify When there is a New Post":
UPDATE forum SET newpostemail = 'X';
where X is a space-separated list of email addresses.

"Email Addresses to Notify When there is a New Thread":
UPDATE forum SET newthreademail = 'X';
where X is a space-separated list of email addresses.

Forum password:
UPDATE forum SET password = 'X';
where X is the password. (Note that you must have "Can Have Password" set to Yes.)

[tubedogg]

As of vB3 Gamma all of the User Option queries can be run by selecting them in the "Automatic Query" box on the Execute Query screen.