Updating Options En Masse with Queries

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Colin F
    Senior Member
    • May 2004
    • 17689

    [vB 3.6] Updating Options En Masse with Queries

    I have copied this thread over from the 3.5 Quick Tips forum since it appears most of these queries work fine to 3.6.x as well. If you find any that don't p[lease fill out a support ticket letting us know.

    To turn your forums off (two separate queries)
    Code:
    UPDATE datastore SET data=REPLACE(data,'s:8:"bbactive";i:1;','s:8:"bbactive";i:0;') WHERE title='options';
    UPDATE setting SET value=0 WHERE varname='bbactive';
    To turn your forums on (two separate queries)
    Code:
    UPDATE datastore SET data=REPLACE(data,'s:8:"bbactive";i:0;','s:8:"bbactive";i:1;') WHERE title='options';
    UPDATE setting SET value=1 WHERE varname='bbactive';
    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

    Show New Private Message Notification Pop-up
    On: UPDATE user SET pmpopup = 1;
    Off: UPDATE user SET pmpopup = 0;

    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
    Best Regards
    Colin Frei

    Please don't contact me per PM.
  • Colin F
    Senior Member
    • May 2004
    • 17689

    #2
    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).

    Style

    UPDATE user SET styleid=X
    where X is the ID of the style (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.).

    Message Editor Interface
    Do Not Show Editor Toolbar: UPDATE user SET showvbcode=0
    Show Standard Editor Toolbar: UPDATE user SET showvbcode=1
    Show Enhanced (WYSIWYG) Editor Toolbar: 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
    Daily email: UPDATE user SET autosubscribe = 2
    Weekly email: UPDATE user SET autosubscribe = 3

    Show Birthday
    Hide Age & Date of Birth: UPDATE user SET showbirthday = 0
    Display Age: UPDATE user SET showbirthday = 1
    Display Age & Date of Birth: UPDATE user SET showbirthday = 2

    Timezoneoffset
    UPDATE user SET timezoneoffset = X
    where X is the GMT value (0, 2, -5, ...)
    Best Regards
    Colin Frei

    Please don't contact me per PM.

    Comment

    • Colin F
      Senior Member
      • May 2004
      • 17689

      #3
      Forum specific queries

      Make all forums active/inactive:
      Active: UPDATE forum SET options=options + 1 WHERE NOT(options & 1);
      Inactive: UPDATE forum SET options=options - 1 WHERE (options & 1);

      Open/Close all forums for new posts:

      Open:
      UPDATE forum SET options=options + 2 WHERE NOT(options & 2);
      Close: UPDATE forum SET options=options - 2 WHERE (options & 2);

      Set all forums to act as categories/forums:

      Act as forums: UPDATE forum SET options=options + 4 WHERE NOT(options & 4);
      Act as categories:UPDATE forum SET options=options - 4 WHERE (options & 4);

      Turn on/off Post Moderation for all forums:
      On: UPDATE forum SET options=options + 8 WHERE NOT(options & 8);
      Off: UPDATE forum SET options=options - 8 WHERE (options & 8);

      Turn on/off Thread Moderation for all forums:
      On: UPDATE forum SET options=options + 16 WHERE NOT(options & 16);
      Off: UPDATE forum SET options=options - 16 WHERE (options & 16);

      Turn on/off Attachment Moderation for all forums:
      On: UPDATE forum SET options=options + 32 WHERE NOT(options & 32);
      Off: UPDATE forum SET options=options - 32 WHERE (options & 32);

      Turn on/off BBCode for all forums:
      On: UPDATE forum SET options=options + 64 WHERE NOT(options & 64);
      Off: UPDATE forum SET options=options - 64 WHERE (options & 64);

      Turn on/off Images for all forums:
      On: UPDATE forum SET options=options + 128 WHERE NOT(options & 128);
      Off: UPDATE forum SET options=options - 128 WHERE (options & 128);

      Turn on/off HTML for all forums:
      On: UPDATE forum SET options=options + 256 WHERE NOT(options & 256);
      Off: UPDATE forum SET options=options - 256 WHERE (options & 256);

      Turn on/off Smilies for all forums:
      On: UPDATE forum SET options=options + 512 WHERE NOT(options & 512);
      Off: UPDATE forum SET options=options - 512 WHERE (options & 512);

      Turn on/off Post Icons for all forums:
      On: UPDATE forum SET options=options + 1024 WHERE NOT(options & 1024);
      Off: UPDATE forum SET options=options - 1024 WHERE (options & 1024);

      Turn on/off Thread Rating for all forums:
      On: UPDATE forum SET options=options + 2048 WHERE NOT(options & 2048);
      Off: UPDATE forum SET options=options - 2048 WHERE (options & 2048);

      Turn on/off Post Counting for all forums:
      On: UPDATE forum SET options=options + 4096 WHERE NOT(options & 4096);
      Off: UPDATE forum SET options=options - 4096 WHERE (options & 4096);

      Set 'Can have Password' on/off for all forums:
      On: UPDATE forum SET options=options + 8192 WHERE NOT(options & 8192);
      Off: UPDATE forum SET options=options - 8192 WHERE (options & 8192);

      Turn on/off Search Indexing for all forums:
      On: UPDATE forum SET options=options + 16384 WHERE NOT(options & 16384);
      Off: UPDATE forum SET options=options - 16384 WHERE (options & 16384);

      Turn on/off Style Overriding for all forums:
      On: UPDATE forum SET options=options + 32768 WHERE NOT(options & 32768);
      Off: UPDATE forum SET options=options - 32768 WHERE (options & 32768);

      Turn on/off 'Show on ForumJump Menu' for all forums:
      On: UPDATE forum SET options=options + 65536 WHERE NOT(options & 65536);
      Off: UPDATE forum SET options=options - 65536 WHERE (options & 65536);

      Turn on/off 'Warn Administrators' for all forums:
      On: UPDATE forum SET options=options + 131072 WHERE NOT(options & 131072);
      Off: UPDATE forum SET options=options - 131072 WHERE (options & 131072);
      Best Regards
      Colin Frei

      Please don't contact me per PM.

      Comment

      • Steve Machol
        Former Customer Support Manager
        • Jul 2000
        • 154488

        #4
        Verified for 3.6.2:

        Turn registration on:
        UPDATE setting SET value = 1 WHERE varname = 'allowregistration';
        UPDATE datastore SET data = REPLACE(data, '"allowregistration";i:0', '"allowregistration";i:1') WHERE title = 'options';

        Turn registration off:
        UPDATE setting SET value = 0 WHERE varname = 'allowregistration';
        UPDATE datastore SET data = REPLACE(data, '"allowregistration";i:1', '"allowregistration";i:0') WHERE title = 'options';
        Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
        Change CKEditor Colors to Match Style (for 4.1.4 and above)

        Steve Machol Photography


        Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


        Comment

        widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
        Working...