Query New in php Template Does Not Run

[josiespencer]

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
// ######################## 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]

$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: " ,
"but i guess that does not belong there.

[josiespencer]

Don't I need ", for continuation lines?

[calorie]

Do not use " ," to separate lines. Do like this:

$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:

$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','lapo ste.net','lemonde.fr','mail.bg',
'mail.com','mail.md','mail.ru','mailru.com','msn.c om','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','earthlin k','eudora','everymail',
'flashmail','france-mail','francemail','freesurf','gmail','go2net','go ogle','grabmail',
'helloasia','hotmail','inbox','lycos','mail','mail 2india','mailnova','mindspring','muslim',
'mymail','netcrawler','netscape','pakistanmail','r ediffmail','searcheurope','virtualindia',
'webmail','yahoo','yandex'
)
");

Though you may wish to consider something like this:

$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]

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]

You guys are so much more helpful than the people at www.vbulletin.com (http://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]

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.