"Receive Email from Administrators": Which members have set this to "no"?

[John Lester]

Originally Posted by kh99

It would be:

SELECT username FROM user WHERE options & 16 = 0

For the "Do not receive email notification upon new pms" change the 16 in the above code to 4096

[WorldCraft]

How do you know which option goes to which bit position value?

[kh99]

Originally Posted by WorldCraft

How do you know which option goes to which bit position value?

The actual values are defined in the file includes/xml/bitfield_vbulletin.xml . The bits used in the user table options field are in a group named "useroptions". But you'd really have to look at the code to figure out which value does what (well, for some you might be able to guess).

Another way you can find out some of the values is to go (in the admicp) to Maintenance > Execute SQL Query, and look at the Automatic Query list. There are automatic queries to set all users to receive admin emails or not, so if you select one of those queries you can see how it's changing the database. If you choose "Yes - Receive Admin Emails" and press continue, you'll see this:

UPDATE user SET options = options + 16 WHERE NOT (options & 16)

and you can tell from that that the value you want for that option is 16. (BTW, you can choose a query from the Automatic Query menu and press Continue, and it will show you the query but not execute it unless you press Continue again).

[WorldCraft]

Thanks Kevin, great information.

[licensinglinks]

Thanks all for your help.

Originally Posted by kh99

It would be:

SELECT username FROM user WHERE options & 16 = 0

Originally Posted by John Lester

For the "Do not receive email notification upon new pms" change the 16 in the above code to 4096

This all worked perfectly. Cheers!

Now I have another question that's related to this. Basically I have set up some custom profile buttons for members' communication preferences. They are simple Yes/No radio buttons:


(1) Newsletter subscription (monthly): Yes/No
(2) Digest subscription (weekly): Yes/No
(3) Promotional email subscription (monthly): Yes/No


I have set every member to "Yes" for both, but I want to change everyone who doesn't receive admin emails to "No" for (1), (2) and (3) and everyone who doesn't want PM notification to "No" for (2). This is to ensure I don't annoy anyone with unwanted emails.

Can anyone please advise what queries I would need to run to do this?

[Simon Lloyd]

Custom profile fields are are in the userfield table, to alter those its something like

PHP Code:

UPDATE`userfield`SET`fieldX`='No' WHERE`fieldX` =Yes; 


so change fieldX to your field number like field10. Dont forget to change the userfield for how it is in your database, if you used a prefix "vb_" then it would be vb_userfield, if you used a prefix say "xy" then it would look like this xyuserfield.

[licensinglinks]

So if, for example, I want to change people everyone who doesn't receive admin emails to "No" for newsletter subscription it would be:

PHP Code:

UPDATE`userfield`SET`field15`='No' WHERE`field16` =Yes; 


(Custom profile field for newsletter subscription (Yes/No) is field15)

Something doesn't seem right about the above code. Shouldn't field 16 =0 ?

[kh99]

You'd need something like:

UPDATE user LEFT JOIN userfield USING(userid) 
   SET fieldX='No', fieldY='No', fieldZ='No' 
   WHERE (options & 16) = 0

and

UPDATE user LEFT JOIN userfield USING(userid) 
   SET fieldY='No' 
   WHERE (options & 4096) = 0

of course you need to set X, Y, and Z to the actual ids.