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

[licensinglinks]

Is there a way of getting a list of usernames who have set it to "no"?

Similarly I would like to know who has selected "Don't receive Email Notification of new Private Messages"

Thanks.

--------------- Added 1357252509 at 1357252509 ---------------

anyone any thoughts?

[licensinglinks]

anyone got any ideas?

[snakes1100]

You would have to create a custom script or use a query directly thru mysql or phpmyadmin.

[licensinglinks]

Yes I know that. Does anyone know what query I would need to run in phpmyadmin?

[Brandon Sheley]

I'd check the user table and see what field that is, then something like
select * from user where admin_emails = no

[Simon Lloyd]

You'd need to know which option in the user table is for recieve admin emails and then get the matches like this
SELECT *
FROM `user`
WHERE substr(`options`, 5, 1) = '1'So this one says show all the people who have the option set for 1 at position 5 in the string of options, so maybe in phpmyadmin the options column looks like 432516780 as you can see in this example position 5 shows 1 so it would be a match.

I dont know what the criteria is for creating the options list or how they arrive at the figures, if i did i could help further :)

--------------- Added 1357315487 at 1357315487 ---------------

I'd check the user table and see what field that is, then something like
select * from user where admin_emails = noUnfortunately that column doesn't exist :(

[kh99]

It would be:
SELECT username FROM user WHERE options & 16 = 0

[Simon Lloyd]

Hi Kevin could you possibly explain the & 16 =0 obviously it works but i don't know why? :)

[kh99]

It's kind of like what you said, only for your method to work the value would have to be a string, with the number represented in binary. The & operator is kind of like treating an integer field as if it were a binary string, and checking only one character out of it. '&' means 'bitwise and', and 16 is 10000 in binary, so it's checking the 5th bit from the right. When you '&' the values, you'll get either 16 or 0, depending on whether or not that bit is set, so then you just have check for whether or not the result is 0.

I hope that makes some sense. Maybe this is better: http://en.wikipedia.org/wiki/Mask_%28computing%29

[Simon Lloyd]

It makes perfect sense, i had the right idea but just not the skill to execute it properly! - thanks for the explanation and it's something i'll add to my toolchest for future :)

[John Lester]

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]

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.

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

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 likeUPDATE`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:

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.