Announcement

Collapse
No announcement yet.

Execute SQL Query- How do I change Custom User Profile Field options, for multiple Usergroups/Users?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • [Forum] Execute SQL Query- How do I change Custom User Profile Field options, for multiple Usergroups/Users?

    vB 4.2.3

    As the title suggest, what would I need to write in the Execute SQL Query option (http://i.imgur.com/WZP9MO6.png), for users/usergroups to have their Custom User Profile Field options forcefully be changed?
    They can still change back from their userCP later.

    This is the code that forces/automatically changes Usergroups X Show Reputation to Hide Reputation in their userCP (http://i.imgur.com/ALEUmXT.png):
    Code:
    UPDATE user SET options=options - 1024 WHERE options & 1024 AND usergroupid IN(X,X,X);
    Example:
    I have a custom user profile field named: Gender [Field5]
    Options:
    Male
    Female
    Hide
    These are the rest of my data for the user profile field: http://i.imgur.com/kDACrUL.png -> Single-Selection Menu.

    How do I change certain users/usergroups just from Male to Hide?
    Also, if I wanted to change certain users/usergroups from Male & Female to Hide, how would I do that?

    Does the Profile Field Type (http://i.imgur.com/1H7oCUJ.png) matter when executing a query like this?
    Last edited by akz645; Tue 22nd Sep '15, 4:03pm.

  • #2
    Try this (untested):

    Code:
    UPDATE userfield SET field5='Hide' where userid in (SELECT userid FROM user WHERE usergroupid IN (X, X, X))
    It is recommended to backup the database before performing this query.

    GIPHY for vB5 AutoLinker Auto-Create Flag Report Topic Social Icons in Postbit Clear Cache Cron DragDrop Upload Topic AJAX AutoUpdate Custom Avatars Selector Stop Links in Posts...and more!

    Comment


    • #3
      Originally posted by Glenn Vergara View Post
      Try this (untested):

      Code:
      UPDATE userfield SET field5='Hide' where userid in (SELECT userid FROM user WHERE usergroupid IN (X, X, X))
      It is recommended to backup the database before performing this query.
      Cheers that worked

      However, is there any way I can get it even more specific?

      So only users who have selected Male gets changed to Hide? Hence all users who have selected Female from the userCP are left unchanged.
      ---
      If I add in the option Bisexual.
      How would I just get only Male & Female to Hide? Hence all users who have selected Bisexual from the userCP are left unchanged.
      (this is just an example, I have other user profile manger which I may need this for)

      Comment


      • #4
        Originally posted by akz645 View Post
        So only users who have selected Male gets changed to Hide? Hence all users who have selected Female from the userCP are left unchanged.
        Code:
        UPDATE userfield SET field5='Hide' where field5='Male' AND userid in (SELECT userid FROM user WHERE usergroupid IN (X, X, X))

        Originally posted by akz645 View Post
        ---
        If I add in the option Bisexual.
        How would I just get only Male & Female to Hide? Hence all users who have selected Bisexual from the userCP are left unchanged.
        (this is just an example, I have other user profile manger which I may need this for)
        Code:
        UPDATE userfield SET field5='Hide' where (field5='Male' OR field5='Female') AND userid in (SELECT userid FROM user WHERE usergroupid IN (X, X, X))

        GIPHY for vB5 AutoLinker Auto-Create Flag Report Topic Social Icons in Postbit Clear Cache Cron DragDrop Upload Topic AJAX AutoUpdate Custom Avatars Selector Stop Links in Posts...and more!

        Comment


        • #5
          Originally posted by Glenn Vergara View Post
          Code:
          UPDATE userfield SET field5='Hide' where field5='Male' AND userid in (SELECT userid FROM user WHERE usergroupid IN (X, X, X))
          Code:
          UPDATE userfield SET field5='Hide' where (field5='Male' OR field5='Female') AND userid in (SELECT userid FROM user WHERE usergroupid IN (X, X, X))
          I just tried both conditions. It works as intended.

          Thank you so much

          I posted your solutions over here:
          http://www.vbulletin.org/forum/showt...97#post2555897

          Comment

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