Managing members waiting for email confirmation

[giaguaro]

due to spam problems, i have set "on" both CAPTCHA, moderation of new users and email confirmation.
Now i have to manage a 16.000 users list.
I would like to manage that in a confortable way, such erasing all the users with a specified email server and so on.
Nowaday, the best function in admin panel is "erase/move members" and i have to check every members.
I dont want to cancel everyone without checking if they are real or not..
Anyone has suggestion on a mod?
Thanks a lot

[kh99]

I don't know of a mod, but I have a suggestion: create a usergroup for users to be deleted. Then use a query to move users to that group, like maybe:
UPDATE user SET usergroupid=X WHERE usergroupid=4 AND email LIKE %hotmail.com%

(usergroupid=4 means users awaiting moderation). You'd replace the X with the usergroupid you created. You can do this multiple times if you have more than one email provider you want to exclude.

Then use Move/Prune users to delete users in usergroupid X.

The safe thing to do would be to have a backup before making any changes directly to the database.

[giaguaro]

this could be great!
But please: where the query must be opened?
I mean, i have to put your code in a file?

[Dave]

You either execute it in something such as PHPMyAdmin or at AdminCP > Maintenance > Execute SQL Query.

[giaguaro]

so, i execute the query and i will find a new members group, with the members awaiting email confirmation speficied in the query?

--------------- Added 1423227234 at 1423227234 ---------------

well, i need the ID of the group "users awaiting email confirmation" to tell the truth

[kh99]

Well, you would first have to go to the usergroup manager and add a new user group. You could use "Create Usergroup Based off of Usergroup"... and select Users Awaiting Moderaton, so that the new usergroup won't have any more permissions. Then note the usergroupid of the group you create and use that in the query.

[SaN-DeeP]

Thanks kh99,
Yet another handy query..

[kh99]

well, i need the ID of the group "users awaiting email confirmation" to tell the truth

Using a query to change the database directly can be dangerous. It might be best not to use this method if you don't feel confident about it, because you don't want to accidentally delete any of your legitimate users.

[giaguaro]

I did mean i dont need usergroup 4 but the id of the "users awaiting email confirmation" group

(omissis)

(usergroupid=4 means users awaiting moderation). You'd replace the X with the usergroupid you created. You can do this multiple times if you have more than one email provider you want to exclude. (omissis)



--------------- Added 1423227773 at 1423227773 ---------------

oh sorry i see i have the ID on the admin panel!

--------------- Added 1423228369 at 1423228369 ---------------

great. My admincp says i am not allowed to launch sql query :(
I am superadmin, dont know why

[kh99]

great. My admincp says i am not allowed to launch sql query :(
I am superadmin, dont know why


You need to list your userid in the config.php file as a user who can run queries. Look for this
$config['SpecialUsers']['canrunqueries'] = '';


and add your userid between the quotes.

[giaguaro]

thanks a lot again. I am really a beginner.. and i am italian :p

[kh99]

well, i need the ID of the group "users awaiting email confirmation" to tell the truth


I misunderstood this. "users awaiting email confirmation" is different than "users awaiting moderation". And I see you found the id in the admincp. Sorry, I thought you were confused, but it was me. :o

[giaguaro]

lol as i told you i am definitely a beginner and have a poor english.

Now:
UPDATE user SET usergroupid=eliminare WHERE usergroupid=3 AND email LIKE %sina.com%

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%sina.com%' at line 1

what happened?

[kh99]

lol as i told you i am definitely a beginner and have a poor english.

Now:
UPDATE user SET usergroupid=eliminare WHERE usergroupid=3 AND email LIKE %sina.com%

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%sina.com%' at line 1

what happened?

Sorry, that's my fault. You need that in quotes, like:
UPDATE user SET usergroupid=eliminare WHERE usergroupid=3 AND email LIKE '%sina.com%'

But also, you can't have 'eliminare' there, because the usergroupid must be a number.

[giaguaro]

great! It worked.
Now i will want to go deeper...
Can i state something like "move all gmail.com account with more than 3 dots in the address"?

--------------- Added 1423324121 at 1423324121 ---------------

or like "everything with .pl suffix"?

[kh99]

So, 3 or more dots other than the one in 'gmail.com'? Maybe this:

SELECT username FROM user WHERE email LIKE '%.%.%.%gmail.com'


and this
SELECT username FROM user WHERE email LIKE '%.pl'


To be honest I'm not sure if trailing spaces are removed from emails addresses or not, so I don't know if you need a trailing '%'.

[giaguaro]

well this work great for registered users.
Will it work also for the quert in user awaiting email confirmation?

One more problem: it doesnt let me erase the users in the new group i created :( ("eliminare")

[kh99]

well this work great for registered users.
Will it work also for the quert in user awaiting email confirmation?

You just need to add "AND usergroupid=3" to the end.


One more problem: it doesnt let me erase the users in the new group i created :( ("eliminare")

What are you trying to do exactly?

[giaguaro]

i go to the erase/move users in the admincp, choose "eliminare" , it list about 5000 users, i flag them all but when i flag "erase" it says "action specified is not valid" and go back to the panel

--------------- Added 1423326747 at 1423326747 ---------------

well to tell the truth it doesnt allow me to move or erase the members of the group i created

[kh99]

Maybe it's too many to delete at one time. Maybe you can use the 'Join Date is Before' to try to select fewer at a time.

[giaguaro]

thanks it worked :) less users, everything ok!

[giaguaro]

that did work again.. so i want to thanks again for the help!

--------------- Added 1611529439 at 1611529439 ---------------

a new request: code to erase directly from query php? thanks in advance