vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB4 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=252)
-   -   MySQL query to find users who don't want email (https://vborg.vbsupport.ru/showthread.php?t=300954)

TerryMason 08-08-2013 01:28 PM
MySQL query to find users who don't want email
 
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.



PHP Code:
    //  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'] . ") )

PHP Code:
        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 08-08-2013 02:33 PM
I think you want to add
Code:
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 08-08-2013 02:59 PM
Thanks kh99. I'm not great with mysql (it's easy to get lost with the " ' ), etc). Does this look right:


PHP Code:
    //  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 08-08-2013 06:46 PM
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 09-12-2013 05:44 PM
I ended up getting this code to work. I can't say that I really understand it though

Code:
        //  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:

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

Do you guys see any problems with this code?


All times are GMT. The time now is 09:35 AM.

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01541 seconds
  • Memory Usage 1,754KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (3)bbcode_code_printable
  • (3)bbcode_php_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (5)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 
Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.php 
Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • printthread_start
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete