XF 1.5 Export all users awaiting email confirmation

[abdfahim]

Can I export all the users awaiting email confirmation along with there email address & date of registration?

I don't need to do it regularly, so not necessarily looking for any feature or add-on, a database query would suffice.

 

[whynot]

Can I export all the users awaiting email confirmation along with there email address & date of registration?

I don't know the answer for that, only wondering: what can you do with them?
They are dead, most probably users created by the spambots.
The email addresses are fictional or someone else's addresses.

 

[abdfahim]

Well, at first I also had the similar thought, but then few things made me rethink:

• The email addresses are very well-formed (usually bot-generated emails use some random or typical phrases)
• Most of the email address are of Hotmail domain, and I know Hotmail doesn't allow any email from my server unless you manually put my domain in your safe sender list
• I have frequent posts from unconfirmed users (I have guest post enabled), which means either they are lazy or, most likely, they didn't see the email
• I have few bot-proof custom fields like Full Name (with a notice, it must be different from the username), Country, Capital etc. I have noticed that usually bots put:
  • Full Name = user name
  • Country = Some well known phrase e.g. Chicago Bulls, LA Lakers etc.
  • Capital: Even if they put some valid country name, they almost certainly put some random capital name, like Bora Bora with Switzerland

That's why I want to send an email from my gmail box to all the users of past few of months before I delete'em all!

 

[ENF]

database query would suffice.

This is a basic query to pull that information:

SELECT username, email, register_date
from xf_user
where user_state = "email_confirm"

Register date is the UNIX time format, so you'd need to convert that to see the actual register date.

Using something like Excel, you can convert all those entries with a simple formula like

=(XX-DATE(1970,1,1))*86400

Replace XX with the record position to convert, A1, A2, etc.

 

[abdfahim]

Awesome, thank you