The Arcive of Official vBulletin Modifications Site.

josiespencer · #1 03-29-2007, 09:29 PM

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 · #2 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 · #3 03-30-2007, 03:54 PM

Don't I need ", for continuation lines?

calorie · #4 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 · #5 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 · #6 03-30-2007, 08:57 PM

Quote:

Originally Posted by josiespencer

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 · #7 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.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.04052 seconds Memory Usage 2,245KB Queries Executed 13 (?)
More Information
Template Usage: (1)SHOWTHREAD (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)ad_showthread_beforeqr (1)ad_showthread_firstpost (1)ad_showthread_firstpost_sig (1)ad_showthread_firstpost_start (3)bbcode_code (3)bbcode_php (1)bbcode_quote (1)footer (1)forumjump (1)forumrules (1)gobutton (1)header (1)headinclude (1)navbar (3)navbar_link (120)option (7)post_thanks_box (7)post_thanks_button (1)post_thanks_javascript (1)post_thanks_navbar_search (7)post_thanks_postbit_info (7)postbit (7)postbit_onlinestatus (7)postbit_wrapper (1)spacer_close (1)spacer_open (1)tagbit_wrapper Phrase Groups Available: global inlinemod postbit posting reputationlevel showthread	Included Files: ./showthread.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/functions_bigthree.php ./includes/class_postbit.php ./includes/class_bbcode.php ./includes/functions_reputation.php ./includes/functions_post_thanks.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_postinfo_query fetch_postinfo fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete showthread_start showthread_getinfo forumjump showthread_post_start showthread_query_postids showthread_query bbcode_fetch_tags bbcode_create showthread_postbit_create postbit_factory postbit_display_start post_thanks_function_post_thanks_off_start post_thanks_function_post_thanks_off_end post_thanks_function_fetch_thanks_start post_thanks_function_fetch_thanks_end post_thanks_function_thanked_already_start post_thanks_function_thanked_already_end fetch_musername postbit_imicons bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete postbit_display_complete post_thanks_function_can_thank_this_post_start tag_fetchbit_complete forumrules navbits navbits_complete showthread_complete
Messages:

The Arcive of Official vBulletin Modifications Site.

It is not a VB3 engine, just a parsed copy!