Remove ICQ/MSN/YAHOO/BIO etc. of around 1600 users via Phpmyadmin.

[SaN-DeeP]

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.

[kh99]

You should be able to do something like this:
UPDATE user SET msn='', icq=''

and of course add whatever other fields you want. Also, for the bio, location, interests, occupation:
UPDATE userfield SET field1='', field2='', field3='', field4=''

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.

[SaN-DeeP]

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,

[kh99]

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:

UPDATE user set password=MD5(CONCAT(MD5('XXYYZZ1122'),salt))


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).

[SaN-DeeP]

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 1423141417 at 1423141417 ---------------

I tried to run the above query..
UPDATE User2 set password=MD5(CONCAT(MD5('XXYYZZ1122'),salt))
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.

[kh99]

You'd want something like this:
UPDATE user SET password=MD5(CONCAT(MD5('XXYYZZ1122'),salt)) WHERE username='User2'


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


For a secondary usergroup, I think this will work:
WHERE FIND_IN_SET(X, membergroupids) != NULL


If you want to test before doing an actual change, you could do something like
SELECT username FROM user WHERE FIND_IN_SET(X, membergroupids) != NULL


and see what gets selected.

Of course in the above queries you want to replace X with the usergroupid you're interested in.

[SaN-DeeP]

Hello once again.
I was able to sucessfully use following command for 1 site:
UPDATE user set password=MD5(CONCAT(MD5('XXYYZZ1122'),salt))
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.
SELECT username FROM vb3_user WHERE FIND_IN_SET(117, membergroupids) = NULL
Gives this:
Results: 0 (2.5048s), Page 1 of 0

UPDATE vb3_user SET password=MD5(CONCAT(MD5('XXYYZZ1122'),salt)) WHERE FIND_IN_SET(117, membergroupids) != NULL
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 ?

[kh99]

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
SELECT username FROM vb3_user WHERE FIND_IN_SET(117, membergroupids) = 0

should give you users who are not in group 117.

[SaN-DeeP]

Yes it worked fine.., gives all users not are not in Group ID - 117

--------------- Added 1423229058 at 1423229058 ---------------

and this
SELECT username FROM vb3_user WHERE FIND_IN_SET(117, membergroupids) = 1
gives all user who are in group 117 properly.

--------------- Added 1423229433 at 1423229433 ---------------

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.

[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

[kh99]

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:
UPDATE vb3_user SET password=MD5(CONCAT(MD5('XXYYZZ1122'),salt)) WHERE FIND_IN_SET(117, membergroupids) != 0


If you aren't sure, you should have a backup first (at least a backup of the user table).

[SaN-DeeP]

Thanks kh99... for best help... Hope there was some kind of more appreciation :)

Dear Dave, thanks ..