vb.org Archive - email query

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB4 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=252)

- - email query (https://vborg.vbsupport.ru/showthread.php?t=315936)

RichieBoy67

12-10-2014 01:36 AM

email query

I am able to do a query to delete users with a certain address but what I want to do is just remove the email address so they do not bounce. This is simple but I would like to do a few other things as well to clear out my user email addresses that are not active.

#1 - Remove certain address from users but keep users. For example remove all email addresses that contain .xmail.com

#2 - Take those users with that address and move them to a different usegroup.

I have something like this

PHP Code:


		
			
UPDATE

user

SET

usergroupid = X

WHERE

  email

LIKE '%.xmail.com'

I have not finished though because I need to remove the address as well. Is this right and what do I add for the delete part just to delete the address and not the entire user?

Thanks my Vbulletin family!:D

ForceHSS

12-10-2014 02:02 AM

PHP Code:


		
			
UPDATE `user` SET `usergroupid`= x WHERE `email` LIKE '%.xmail.com'

this will change usergroupid to what you set x to

Lynne

12-10-2014 02:07 AM

Just a note to say you should always test queries on a test site first and before doing so on the live site make sure to make a database backup.

RichieBoy67

12-10-2014 04:13 PM

Quote:

Originally Posted by ForceHSS (Post 2526545)

PHP Code:


		
			
UPDATE `user` SET `usergroupid`= x WHERE `email` LIKE '%.xmail.com'

this will change usergroupid to what you set x to

Yeah, that is only the first part of the query. Not only do I want to change the usergroup of those with certain email addresses but i want to remove those addresses from the user accounts. This is the part of the query I am having trouble with.

Thanks for the reply my friend!

--------------- Added [DATE]1418235303[/DATE] at [TIME]1418235303[/TIME] ---------------

Quote:

Originally Posted by Lynne (Post 2526546)

Just a note to say you should always test queries on a test site first and before doing so on the live site make sure to make a database backup.

Thanks Lynn, I will make a back up before hand but I am not going to run anything until I am sure it is what I want.

Dave	12-10-2014 04:25 PM

You want to remove the address as in setting the email column to an empty value? Or do you want to remove a specific part of the email address?

RichieBoy67

12-10-2014 04:34 PM

The entire address. I do not want to lose those members but I do not want those addresses to receive any type of email so I just want to remove the entire address from the user accounts.

Thanks dave

Dave	12-10-2014 04:43 PM

It's a matter of changing the email column to an empty string:

PHP Code:


		
			
UPDATE `user` SET `usergroupid`= x, `email` = '' WHERE `email` LIKE '%.xmail.com'

When you create a new user in the ACP of vBulletin and don't enter an email, vBulletin will do the same and set it to an empty string.

ForceHSS

12-10-2014 04:59 PM

Quote:

Originally Posted by Dave (Post 2526631)

It's a matter of changing the email column to an empty string:

PHP Code:


		
			
UPDATE `user` SET `usergroupid`= x, `email` = '' WHERE `email` LIKE '%.xmail.com'

When you create a new user in the ACP of vBulletin and don't enter an email, vBulletin will do the same and set it to an empty string.

tested that myself as I had a feeling it would not work
after you have changed usergroupid try this
UPDATE `user` SET `email`='' WHERE `usergroupid`= x

RichieBoy67

12-10-2014 05:11 PM

Quote:

Originally Posted by Dave (Post 2526631)

It's a matter of changing the email column to an empty string:

PHP Code:


		
			
UPDATE `user` SET `usergroupid`= x, `email` = '' WHERE `email` LIKE '%.xmail.com'

When you create a new user in the ACP of vBulletin and don't enter an email, vBulletin will do the same and set it to an empty string.

yeah, I tried this early but it would not work for me though I could have had it wrong. Thanks much

--------------- Added 10 Dec 2014 at 14:12 ---------------

Quote:

Originally Posted by ForceHSS (Post 2526635)

tested that myself as I had a feeling it would not work
after you have changed usergroupid try this
UPDATE `user` SET `email`='' WHERE `usergroupid`= x

That makes sense to do them separately.

Thanks guys!!

ForceHSS

12-10-2014 05:18 PM

Happy to help

All times are GMT. The time now is 02:55 AM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.00997 seconds Memory Usage 1,745KB 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 (6)bbcode_php_printable (5)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (10)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
Messages: