vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)
-   -   Query New in php Template Does Not Run (https://vborg.vbsupport.ru/showthread.php?t=143544)

josiespencer 03-29-2007 09:29 PM
Query New in php Template Does Not Run
 
I have scheduled this SQL query to run every 10 minutes but since it is not changing any data, I have to assume it has a problem. I can manually run it and it does not abend, it just comes back with a done.

The SQL statement is to update all user rows having a usergroupid of 2 to a usergroupid of 105 and displaygroup id of 105 if their email address is not tagged as a free email service. This is my code:

PHP Code:
<?php
// ######################## SET PHP ENVIRONMENT ###########################
error_reporting(E_ALL & ~E_NOTICE);
if (!is_object($vbulletin->db))
{
    exit;
}
// ########################################################################
// ######################### START MAIN SCRIPT ############################
// ########################################################################

$vbulletin->db->query_write("UPDATE " .TABLE_PREFIX. "user SET usergroupid = 105, displaygroupid = 105 WHERE usergroupid = 2 AND email NOT LIKE '%gmail%' AND " ,
"email NOT LIKE '%hotmail%' AND email NOT LIKE '%rediffmail%' AND email NOT LIKE '%yahoo%' AND email NOT LIKE '%msn.com%' AND " ,
"email NOT LIKE '%web.de%' AND email NOT LIKE '%rediffmail%' AND email NOT LIKE '%google%' AND email NOT LIKE '%@mail.com%' AND " ,
"email NOT LIKE '%excite.com%' AND email NOT LIKE '%@mail.ru%' AND email NOT LIKE '%yandex%' AND email NOT LIKE '%free.fr%' AND " ,
"email NOT LIKE '%t-online.de%' AND email NOT LIKE '%web2mail.com%' AND email NOT LIKE '%rocketmail.com%' AND email NOT LIKE '%pookmail.com%' AND " ,
"email NOT LIKE '%@email.com%' AND email NOT LIKE '%webmail%' AND email NOT LIKE '%mindspring%' AND " ,
"email NOT LIKE '%earthlink%' AND email NOT LIKE '%pandora.be%' AND email NOT LIKE '%mail.bg%' AND email NOT LIKE '%swing.be%' AND " ,
"email NOT LIKE '%inbox%' AND email NOT LIKE '%ifrance.com%' AND email NOT LIKE '%free.hu%' AND email NOT LIKE '%@elvis.com%' AND " ,
"email NOT LIKE '%eudora%' AND email NOT LIKE '%go2net%'  AND email NOT LIKE '%flashmail%' AND email NOT LIKE '%@la.com%' AND email NOT LIKE '%lycos%' AND " ,
"email NOT LIKE '%muslim%' AND email NOT LIKE '%netscape%'  AND email NOT LIKE '%@themail.com%' AND email NOT LIKE '%@usa.com%' AND " ,
"email NOT LIKE '%india-11.com%' AND email NOT LIKE '%123india%' AND email NOT LIKE '%ifrance.com%' AND email NOT LIKE '%@india.com%' AND " ,
"email NOT LIKE '%indiatimes.com%' AND email NOT LIKE '%mail2india%' AND email NOT LIKE '%mailnova%' AND email NOT LIKE '%ChequeMail.com%' AND " ,
"email NOT LIKE '%mail2india%' AND email NOT LIKE '%netcrawler%' AND email NOT LIKE '%@paki.com%' AND email NOT LIKE '%pakistanmail%' AND " ,
"email NOT LIKE '%helloasia%' AND email NOT LIKE '%virtualindia%' AND email NOT LIKE '%grabmail%' AND email NOT LIKE '%@mailru.com%' AND " ,
"email NOT LIKE '%chat.ru%' AND email NOT LIKE '%@mail.md%' AND email NOT LIKE '%@tut.by%' AND email NOT LIKE '%@abv.bg%' AND " ,
"email NOT LIKE '%@all.bg%' AND email NOT LIKE '%searcheurope%' AND email NOT LIKE '%email.co.yu%' AND email NOT LIKE '%newmail.net%' AND " ,
"email NOT LIKE '%@mail.%' AND email NOT LIKE '%iobox.fi%' AND email NOT LIKE '%spray.no%' AND email NOT LIKE '%altavista%' AND " ,
"email NOT LIKE '%a-topmail%' AND email NOT LIKE '%everymail%' AND email NOT LIKE '%belgique%' AND email NOT LIKE '%3xl.net%' AND " ,
"email NOT LIKE '%mail2india%' AND email NOT LIKE '%francemail%' AND email NOT LIKE '%france-mail%' AND email NOT LIKE '%laposte.net%' AND " ,
"email NOT LIKE '%lemonde.fr%' AND email NOT LIKE '%axis.org%' AND email NOT LIKE '%mymail%' AND email NOT LIKE '%163.com%' AND " ,
"email NOT LIKE '%gmx.de%' AND email NOT LIKE '%freesurf%' AND email NOT LIKE '%axis.org%' AND email NOT LIKE '%casino%'");

log_cron_action('', $nextitem, 1);

/*======================================================================*\
|| ####################################################################
|| # Written for SAP Super Users
|| ####################################################################
\*======================================================================*/
?>
Any assistance would be very appreciated - or even an alternate way to do this is welcome!

Marco van Herwaarden 03-30-2007 06:48 AM
PHP Code:
$vbulletin->db->query_write("UPDATE " .TABLE_PREFIX. "user SET usergroupid = 105, displaygroupid = 105 WHERE usergroupid = 2 AND email NOT LIKE '%gmail%' AND " ,
"email NOT LIKE 
Not sure what the following is doing in your code:
PHP Code:
" ,
" 
but i guess that does not belong there.

josiespencer 03-30-2007 03:54 PM
Don't I need ", for continuation lines?

calorie 03-30-2007 05:21 PM
Do not use " ," to separate lines. Do like this:
Code:
$vbulletin->db->query_write("
        UPDATE " . TABLE_PREFIX . "user
        SET usergroupid = 105, displaygroupid = 105
        WHERE usergroupid = 2
        AND email NOT LIKE '%gmail%' AND email NOT LIKE '%hotmail%' AND email NOT LIKE '%rediffmail%'
        AND email NOT LIKE '%yahoo%' AND email NOT LIKE '%msn.com%' AND email NOT LIKE '%web.de%'
        AND email NOT LIKE '%rediffmail%' AND email NOT LIKE '%google%' AND email NOT LIKE '%@mail.com%'
        AND email NOT LIKE '%excite.com%' AND email NOT LIKE '%@mail.ru%' AND email NOT LIKE '%yandex%'
        AND email NOT LIKE '%free.fr%' AND email NOT LIKE '%t-online.de%' AND email NOT LIKE '%web2mail.com%'
        AND email NOT LIKE '%rocketmail.com%' AND email NOT LIKE '%pookmail.com%' AND email NOT LIKE '%@email.com%'
        AND email NOT LIKE '%webmail%' AND email NOT LIKE '%mindspring%' AND email NOT LIKE '%earthlink%'
        AND email NOT LIKE '%pandora.be%' AND email NOT LIKE '%mail.bg%' AND email NOT LIKE '%swing.be%'
        AND email NOT LIKE '%inbox%' AND email NOT LIKE '%ifrance.com%' AND email NOT LIKE '%free.hu%'
        AND email NOT LIKE '%@elvis.com%' AND email NOT LIKE '%eudora%' AND email NOT LIKE '%go2net%'
        AND email NOT LIKE '%flashmail%' AND email NOT LIKE '%@la.com%' AND email NOT LIKE '%lycos%'
        AND email NOT LIKE '%muslim%' AND email NOT LIKE '%netscape%'  AND email NOT LIKE '%@themail.com%'
        AND email NOT LIKE '%@usa.com%' AND email NOT LIKE '%india-11.com%' AND email NOT LIKE '%123india%'
        AND email NOT LIKE '%ifrance.com%' AND email NOT LIKE '%@india.com%' AND email NOT LIKE '%indiatimes.com%'
        AND email NOT LIKE '%mailnova%' AND email NOT LIKE '%chequemail.com%' AND email NOT LIKE '%mail2india%'
        AND email NOT LIKE '%netcrawler%' AND email NOT LIKE '%@paki.com%' AND email NOT LIKE '%pakistanmail%'
        AND email NOT LIKE '%helloasia%' AND email NOT LIKE '%virtualindia%' AND email NOT LIKE '%grabmail%'
        AND email NOT LIKE '%@mailru.com%' AND email NOT LIKE '%chat.ru%' AND email NOT LIKE '%@mail.md%'
        AND email NOT LIKE '%@tut.by%' AND email NOT LIKE '%@abv.bg%' AND email NOT LIKE '%@all.bg%'
        AND email NOT LIKE '%searcheurope%' AND email NOT LIKE '%email.co.yu%' AND email NOT LIKE '%newmail.net%'
        AND email NOT LIKE '%@mail.%' AND email NOT LIKE '%iobox.fi%' AND email NOT LIKE '%spray.no%'
        AND email NOT LIKE '%altavista%' AND email NOT LIKE '%a-topmail%' AND email NOT LIKE '%everymail%'
        AND email NOT LIKE '%belgique%' AND email NOT LIKE '%3xl.net%' AND email NOT LIKE '%francemail%'
        AND email NOT LIKE '%france-mail%' AND email NOT LIKE '%laposte.net%' AND email NOT LIKE '%lemonde.fr%'
        AND email NOT LIKE '%axis.org%' AND email NOT LIKE '%mymail%' AND email NOT LIKE '%163.com%'
        AND email NOT LIKE '%gmx.de%' AND email NOT LIKE '%freesurf%' AND email NOT LIKE '%casino%'
");
You might try something like this instead. Tweak as needed:
Code:
$vbulletin->db->query_write("
        UPDATE " . TABLE_PREFIX . "user
        SET usergroupid = 105, displaygroupid = 105
        WHERE usergroupid = 2
        AND SUBSTRING_INDEX(email, '@', -1) NOT IN
                (
                        '163.com','3xl.net','abv.bg','all.bg','axis.org','chat.ru','chequemail.com','elvis.com',
                        'email.co.yu','email.com','excite.com','free.fr','free.hu','gmx.de','ifrance.com','india.com',
                        'india-11.com','indiatimes.com','iobox.fi','la.com','laposte.net','lemonde.fr','mail.bg',
                        'mail.com','mail.md','mail.ru','mailru.com','msn.com','newmail.net','paki.com','pandora.be',
                        'pookmail.com','rocketmail.com','spray.no','swing.be','t-online.de','themail.com','tut.by',
                        'usa.com','web.de','web2mail.com'
                )
        AND SUBSTRING_INDEX(SUBSTRING_INDEX(email, '@', -1), '.', 1) NOT IN
                (
                        '123india','a-topmail','altavista','belgique','casino','earthlink','eudora','everymail',
                        'flashmail','france-mail','francemail','freesurf','gmail','go2net','google','grabmail',
                        'helloasia','hotmail','inbox','lycos','mail','mail2india','mailnova','mindspring','muslim',
                        'mymail','netcrawler','netscape','pakistanmail','rediffmail','searcheurope','virtualindia',
                        'webmail','yahoo','yandex'
                )
");
Though you may wish to consider something like this:
Code:
$vbulletin->db->query_write("
        UPDATE " . TABLE_PREFIX . "user
        SET usergroupid = 105, displaygroupid = 105
        WHERE usergroupid = 2
        AND SUBSTRING_INDEX(email, '@', -1) NOT IN
                (
                        SELECT banemail FROM banemails
                )
");
Where the table banemails contains foo.com entries.

josiespencer 03-30-2007 07:22 PM
Thanks! I can't use option 3 since we don't want to ban those users, just not give them rights until they have proven themselves.

I will try this out right now! I really appreciate the help.

I just wanted to come back and tell you that removing the ", from the end of the lines did the trick. You guys are so much more helpful than the people at www.vbulletin.com!

Marco van Herwaarden 03-30-2007 08:57 PM
Quote:
Originally Posted by josiespencer (Post 1216351)
You guys are so much more helpful than the people at www.vbulletin.com!
Funny, as some provide support on both sites.

You will need to understand the difference between the sites though:

vBulletin.com: Official Support with your standard vBulletin.
vBulletin.org: Member to Member support for modifying vBulletin.

josiespencer 03-30-2007 09:02 PM
Actually, the difference is more than that.

vBulletin.com: Will try to talk you out of what you need to do.
vBulletin.org: Just helps you do what you need to do regardless.


All times are GMT. The time now is 07:01 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.01130 seconds
  • Memory Usage 1,790KB
  • 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)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (7)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