Updating Options en masse

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

    Updating Options en masse

    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;

    Receive Private Messages only from Buddies
    On: UPDATE user SET options=options + 131072 WHERE NOT(options & 131072);
    Off: UPDATE user SET options=options - 131072 WHERE options & 131072;

    Show User CSS on Profiles
    On: UPDATE user SET options=options + 1048576 WHERE NOT(options & 1048576);
    Off: UPDATE user SET options=options - 1048576 WHERE options & 1048576;

    Enable Visitor Messages
    On: UPDATE user SET options=options + 8388608 WHERE NOT(options & 8388608);
    Off: UPDATE user SET options=options - 8388608 WHERE options & 8388608;

    Limit Visitor Messages to Contacts

    On: UPDATE user SET options=options + 16777216 WHERE NOT(options & 16777216);
    Off: UPDATE user SET options=options - 16777216 WHERE options & 16777216;

    Save Copy of PMs by Default

    On: UPDATE user SET options=options + 33554432 WHERE NOT(options & 33554432);
    Off: UPDATE user SET options=options - 33554432 WHERE options & 33554432;
    Last edited by Colin F; Wed 11 Mar '09, 8:21am.
    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:

    Thread Display Mode
    Linear: UPDATE user SET threadedmode=0;
    Threaded: UPDATE user SET threadedmode=1;
    Hybrid: UPDATE user SET threadedmode=2;

    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, ...)

    Allow use of admin-set custom avatar
    On: UPDATE user SET adminoptions=adminoptions + 1 WHERE NOT(adminoptions & 1);
    Off: UPDATE user SET adminoptions=adminoptions - 1 WHERE adminoptions & 1;

    Allow use of admin-set custom profile picture
    On: UPDATE user SET adminoptions=adminoptions + 2 WHERE NOT(adminoptions & 2);
    Off: UPDATE user SET adminoptions=adminoptions - 2 WHERE adminoptions & 2;
    Last edited by Colin F; Wed 30 Aug '06, 4:18am.
    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);
      If this is turned On, then the styleid should be updated for all the forums as well, by running this query, where X is the styleid number:UPDATE forum SET styleid = X;

      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);

      Change 'Default Sort Field' for all forums:
      Thread Title: UPDATE forum SET defaultsortfield = 'title';
      Last Post Time:
      UPDATE forum SET defaultsortfield = 'lastpost';
      Thread Start Time:
      UPDATE forum SET defaultsortfield = 'dateline';
      Number of Replies:
      UPDATE forum SET defaultsortfield = 'replycount';
      Number of Views:
      UPDATE forum SET defaultsortfield = 'viewcount';
      Thread Starter:
      UPDATE forum SET defaultsortfield = 'postusername';
      Thread Rating:
      UPDATE forum SET defaultsortfield = 'voteavg';

      Change 'Default Sort Order' for all forums:
      Descending: UPDATE forum SET defaultsortorder = 'desc';
      Ascending: UPDATE forum SET defaultsortorder = 'asc';
      Last edited by Colin F; Wed 4 Oct '06, 8:09am.
      Best Regards
      Colin Frei

      Please don't contact me per PM.

      Comment

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