Register Members List Search Today's Posts Mark Forums Read

Reply
 
Thread Tools
  #1  
Old 05 Feb 2015, 09:33
SaN-DeeP's Avatar
SaN-DeeP SaN-DeeP is offline
 
Join Date: Jun 2002
Question Remove ICQ/MSN/YAHOO/BIO etc. of around 1600 users via Phpmyadmin.

I was just working on a forum setup few years ago.. then took offline.
It has around 1540 users. (mostly spammers)
I dont want to delete those members. Is there anyway I can remove there ICQ/MSN/BIO/INTERESTS and other details directly via PHPMYADMIN ?

Thanks.
Reply With Quote
  #2  
Old 05 Feb 2015, 11:29
kh99 kh99 is offline
 
Join Date: Aug 2009
Real name: Kevin
You should be able to do something like this:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

and of course add whatever other fields you want. Also, for the bio, location, interests, occupation:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

and if you have other custom profile fields you could add them to that query.

As always, you should make sure you have a database backup before modifying the database directly, just in case.

Edit: you could also add "WHERE usergroupid=X" to do it only for a certain group, if you have other users (admins and mods) that you don't want to change.
Reply With Quote
  #3  
Old 05 Feb 2015, 11:36
SaN-DeeP's Avatar
SaN-DeeP SaN-DeeP is offline
 
Join Date: Jun 2002
Dear kh99,
You saved me lot of time...

I want to few more advanced things if possible.

1. Change username of those users from xyz2 to abc1
Can i directly edit this from phpmyadmin ?
Opening the DB in phpmyadmin and changing the usernames for faster process.

2. Can I also change passwords of this usergroups to something UNIQUE ?
Change passwords for all users to XXYYZZ1122.
I tried to setup this password in PHPMYADMIN, but that wont help me login. (gives wrong password)

Regards,
Reply With Quote
  #4  
Old 05 Feb 2015, 11:42
kh99 kh99 is offline
 
Join Date: Aug 2009
Real name: Kevin
I can't think of any reason offhand that you couldn't change the username field directly, except that I think there are other tables that have a username field (like the thread last poster) that won't be changed.

You should be able to change the password, but it's hashed. I think you'd need to do something like:


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.


but I haven't tested this. Be careful because as I'm sure you're aware, if it doesn't work you won't be able to log in either (unless you add a WHERE so that your password isn't changed).
Reply With Quote
  #5  
Old 05 Feb 2015, 11:49
SaN-DeeP's Avatar
SaN-DeeP SaN-DeeP is offline
 
Join Date: Jun 2002
Dear kh99,
Thanks again for keeping with me and your quick answers.

The database only has users NO CONTENT/NO POSTS/NO THREADS..
I dont want to delete all the users, as those were registered few years ago.. and its from a VERY UNIQUE NICHE..
But we have around 500+ banned spammers as well..
We promoted that site few years ago, then closed because of busy schedule. time to re-launch again soon.

I want to run a query.. which can change passwords for all users who belong to a specific usergroup (primary or secondary) to fasten my process.
I will manage changing the usernames manually via PHPMYADMIN which works perfect..


THANKS FOR suggesting backup.. taking a new backup everytime after successfully accomplishing above tasks in proper order.

*Hardly few MBs back* directly storing on server, naming appropriately.

Regards,

--------------- Added 05 Feb 2015 at 12:03 ---------------

I tried to run the above query..

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

User2 = the username I am trying to change.

Getting following error:
#1146 - Table 'DBi_dbname.User2' doesn't exist

Not a techy to frame query or code.

Last edited by SaN-DeeP; 05 Feb 2015 at 12:04.
Reply With Quote
  #6  
Old 05 Feb 2015, 12:18
kh99 kh99 is offline
 
Join Date: Aug 2009
Real name: Kevin
You'd want something like this:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.


If you want to do it for a primary usergroup, you'd change the WHERE part to

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.


For a secondary usergroup, I think this will work:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.


If you want to test before doing an actual change, you could do something like

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.


and see what gets selected.

Of course in the above queries you want to replace X with the usergroupid you're interested in.
Reply With Quote
  #7  
Old 06 Feb 2015, 11:53
SaN-DeeP's Avatar
SaN-DeeP SaN-DeeP is offline
 
Join Date: Jun 2002
Hello once again.
I was able to sucessfully use following command for 1 site:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

Which successfully worked for all users.. Big thanks..

***************************************************************

But for another complex site.. which has few different user groups..
(Many Paid members here offline/online payment method, differentiated into multiple groups by mode of payment/amount etc.)

Example:
UserOne belongs to GROUP NAME Members ID = 2
UserOne is also under another GROUP NAME Paid Donors ID = 117 - This is like an Additional Group member belongs too.

Trying to run the queries you guided me with, these are results.

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

Gives this:
Results: 0 (2.5048s), Page 1 of 0


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

Gives this:
Confirm Query Execution
This query may modify data in your database. If this change is done in error, it is possible that you will not be able to recover from this change. Are you sure you wish to continue?

Affected Rows: 0 (3.3929s)

There are many users in Group ID - 117

Seems I am missing something ?
Reply With Quote
  #8  
Old 06 Feb 2015, 12:13
kh99 kh99 is offline
 
Join Date: Aug 2009
Real name: Kevin
Well, I have to admit that I didn't try it. I think the problem is that you have to check for 0 instead of NULL. So

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

should give you users who are not in group 117.
Reply With Quote
  #9  
Old 06 Feb 2015, 12:22
SaN-DeeP's Avatar
SaN-DeeP SaN-DeeP is offline
 
Join Date: Jun 2002
Yes it worked fine.., gives all users not are not in Group ID - 117

--------------- Added 06 Feb 2015 at 12:24 ---------------

and this

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

gives all user who are in group 117 properly.

--------------- Added 06 Feb 2015 at 12:30 ---------------

should i try
UPDATE vb3_user SET password=MD5(CONCAT(MD5('XXYYZZ1122'),salt)) WHERE FIND_IN_SET(117, membergroupids) != 1
OR
UPDATE vb3_user SET password=MD5(CONCAT(MD5('XXYYZZ1122'),salt)) WHERE FIND_IN_SET(117, membergroupids) != 0

dont want to risk.
Reply With Quote
  #10  
Old 06 Feb 2015, 12:38
Dave Dave is offline
 
Join Date: Jun 2010
Real name: Dave
If you're unsure, change your query to SELECT first to see which rows will be affected.

SELECT * FROM vb3_user WHERE FIND_IN_SET(117, membergroupids) != 1
--
SELECT * FROM vb3_user WHERE FIND_IN_SET(117, membergroupids) != 0
__________________
https://technidev.com - security, development, exploits, vBulletin
dave[at]technidev[dot]com

Contact me for custom vBulletin 3/4 work & server/website management.
Reply With Quote
  #11  
Old 06 Feb 2015, 12:43
kh99 kh99 is offline
 
Join Date: Aug 2009
Real name: Kevin
If you want to select all users who are in group 117, you should use "!= 0", since "=1" will check to see if it is the first group listed, if a user belongs to multiple secondary groups. So if you want to change the password for all users who are in group 117, you should use this:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.


If you aren't sure, you should have a backup first (at least a backup of the user table).
Reply With Quote
  #12  
Old 06 Feb 2015, 17:16
SaN-DeeP's Avatar
SaN-DeeP SaN-DeeP is offline
 
Join Date: Jun 2002
Thanks kh99... for best help... Hope there was some kind of more appreciation

Dear Dave, thanks ..
Reply With Quote
Reply



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


New To Site? Need Help?

All times are GMT. The time now is 10:42.

Layout Options | Width: Wide Color: