MySQL query to find users who don't want email

[TerryMason]

I'm looking to update the below mysql query to look at the "receive admin email" setting in vbulletin, and only select those that are set to YES.




// Get Members
$members = $db->query_read("
SELECT user.email, user.userid, user.username " . iif((!empty($vbulletin->options['c_news_letter_field'])),",userfield." . $vbulletin->options['c_news_letter_field'] . "") . "
FROM " . TABLE_PREFIX . "user AS user
" . iif((!empty($vbulletin->options['c_news_letter_field'])),"LEFT JOIN " . TABLE_PREFIX . "userfield AS userfield ON (userfield.userid = user.userid)") . "
WHERE user.userid >= " . $vbulletin->GPC['startat'] . "
" . iif((!empty($vbulletin->options['c_news_letter_field']))," AND (userfield." . $vbulletin->options[c_news_letter_field] . " = '" . $vbulletin->options[c_news_letter_field_yes] . "' OR userfield." . $vbulletin->options[c_news_letter_field] . " = '')") . "
" . iif((!empty($vbulletin->GPC['usergroups'])), "AND user.usergroupid IN ('" . implode("','",$c_news_letter_filter_usergroups) . "')") . "
ORDER BY user.userid ASC
LIMIT " . $vbulletin->GPC['perpage']
);




I *think* it should be something like this (I added AND userfield." . $vbulletin->options['16'] . ") )


WHERE user.userid >= " . $vbulletin->GPC['startat'] . "
" . iif((!empty($vbulletin->options['c_news_letter_field']))," AND (userfield." . $vbulletin->options[c_news_letter_field] . " = '" . $vbulletin->options[c_news_letter_field_yes] . "' OR userfield." . $vbulletin->options[c_news_letter_field] . " = '') AND userfield." . $vbulletin->options['16'] . ") ") . "
" . iif((!empty($vbulletin->GPC['usergroups'])), "AND user.usergroupid IN ('" . implode("','",$c_news_letter_filter_usergroups) . "')") . "
ORDER BY user.userid ASC



Does that seem right?

[kh99]

I think you want to add
AND (user.options & " . $vbulletin->bf_misc_useroptions['adminemail'] . ")


ETA: you'd also need to add user.options to the list of fields being selected.

[TerryMason]

Thanks kh99. I'm not great with mysql (it's easy to get lost with the " ' ), etc). Does this look right:



// Get Members
$members = $db->query_read("
SELECT user.email, user.userid, user.username, user.options " . iif((!empty($vbulletin->options['c_news_letter_field'])),",userfield." . $vbulletin->options['c_news_letter_field'] . "") . "
FROM " . TABLE_PREFIX . "user AS user
" . iif((!empty($vbulletin->options['c_news_letter_field'])),"LEFT JOIN " . TABLE_PREFIX . "userfield AS userfield ON (userfield.userid = user.userid)") . "
WHERE user.userid >= " . $vbulletin->GPC['startat'] . "
" . iif((!empty($vbulletin->options['c_news_letter_field']))," AND (userfield." . $vbulletin->options[c_news_letter_field] . " = '" . $vbulletin->options[c_news_letter_field_yes] . "' OR userfield." . $vbulletin->options[c_news_letter_field] . " = '')" AND (user.options & " . $vbulletin->bf_misc_useroptions['adminemail'] . ") ) . "
" . iif((!empty($vbulletin->GPC['usergroups'])), "AND user.usergroupid IN ('" . implode("','",$c_news_letter_filter_usergroups) . "')") . "
ORDER BY user.userid ASC
LIMIT " . $vbulletin->GPC['perpage']
);

[kh99]

I have trouble sorting out that stuff just by looking at it, but I think you have one extra double quote char before the AND you added.

[TerryMason]

I ended up getting this code to work. I can't say that I really understand it though


// Get Members
$members = $db->query_read("
SELECT user.email, user.userid, user.username, user.options " . iif((!empty($vbulletin->options['c_news_letter_field'])),",userfield." . $vbulletin->options['c_news_letter_field'] . "") . "
FROM " . TABLE_PREFIX . "user AS user
" . iif((!empty($vbulletin->options['c_news_letter_field'])),"LEFT JOIN " . TABLE_PREFIX . "userfield AS userfield ON (userfield.userid = user.userid)") . "
WHERE user.userid >= " . $vbulletin->GPC['startat'] . "
" . iif((!empty($vbulletin->options['c_news_letter_field']))," AND (userfield." . $vbulletin->options[c_news_letter_field] . " = '" . $vbulletin->options[c_news_letter_field_yes] . "' OR userfield." . $vbulletin->options[c_news_letter_field] . " = '')") . "

" . iif(!$vbulletin->GPC['user']['adminemail'], " AND (options & " . $vbulletin->bf_misc_useroptions['adminemail'] . ")") . "

" . iif((!empty($vbulletin->GPC['usergroups'])), "AND user.usergroupid IN ('" . implode("','",$c_news_letter_filter_usergroups) . "')") . "
ORDER BY user.userid ASC
LIMIT " . $vbulletin->GPC['perpage']
);



this was the line that did the heavy lifting:

" . iif(!$vbulletin->GPC['user']['adminemail'], " AND (options & " . $vbulletin->bf_misc_useroptions['adminemail'] . ")") . "


Do you guys see any problems with this code?