phpmyadmin question

Get help with installation and running phpBB 3.2.x here. Please do not post bug reports, feature requests, or extension related questions here.
Post Reply
sekellas
Registered User
Posts: 7
Joined: Fri Jan 18, 2019 10:13 pm

phpmyadmin question

Post by sekellas » Wed May 22, 2019 9:58 pm

Hey all,

I had our forums back-end dropped on me a while ago, and while I've learned a lot since, I am still trying to learn everything I need to moderate as well as support our staff who want to be able to reach out to all of our members at the same time. I've been asked to create a list from the forum database of all active members' email addresses. However, sending a message via the forums stalls at 100 emails because our host limits us to 100 emails per day, and we have probably 300 active members we want to reach out to. So we're going to use another service to actually reach out to our members. Hence the need for the list.

I know that I can run a query in phpmyadmin and get email addresses, but I have no idea how to actually do it.

Is there anyone who could walk me through the process, privately or here?

I'd like to be able to make sure that only people who have logged in and posted in the last year get the email, if that helps with how to set up the query.


Thanks very much!

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 50426
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: phpmyadmin question

Post by stevemaury » Thu May 23, 2019 3:19 am

Executing SQL Queries in phpMyAdmin

Code: Select all

SELECT user_email FROM phpbb_users WHERE user_type <>2 AND user_lastpost_time >  UNIX_TIMESTAMP() - 31536000 
If your table prefix is not phpbb_ change accordingly.

You will want to export the query result as CSV, which will give you a file you can past into an email client group.
Last edited by stevemaury on Thu May 23, 2019 4:39 pm, edited 1 time in total.
Reason: Edited to replace NOW() with UNIX_TIMESTAMP()
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)

User avatar
janus_zonstraal
Registered User
Posts: 3220
Joined: Sat Aug 30, 2014 1:30 pm

Re: phpmyadmin question

Post by janus_zonstraal » Thu May 23, 2019 6:44 am

Or use this extension
viewtopic.php?t=2326086
Sorry! My English is bat ;) !!!

User avatar
AmigoJack
Registered User
Posts: 5573
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン
Contact:

Re: phpmyadmin question

Post by AmigoJack » Thu May 23, 2019 7:25 am

The query above neither honors the user setting "Administrators can send me e-mails" (those who unticked it will still be in your address list), nor banned users. A more appropriate query would be (in MySQL/MariaDB slang):

Code: Select all

SELECT DISTINCT u.user_email  -- Fold identical address into being listed only once
  FROM phpbb_users u
  LEFT JOIN phpbb_banlist bu ON bu.ban_userid= u.user_id  -- Exclude bans per member ID
  LEFT JOIN phpbb_banlist be ON be.ban_email= u.user_email  -- Exclude bans per e-mail address
 WHERE u.user_type IN( 0, 3 )  -- Only normal and founders, neither deactivated nor ignored
   AND u.user_email<> ''  -- Empty addresses might occur
   AND u.user_allow_massemail<> 0  -- Only members who opted in
   AND coalesce( bu.ban_start, unix_timestamp() )<= unix_timestamp()
   AND coalesce( bu.ban_end, unix_timestamp()+ 1 )>= unix_timestamp()
   AND (bu.ban_userid IS NULL OR bu.ban_exclude<> 0)
   AND coalesce( be.ban_start, unix_timestamp() )<= unix_timestamp()
   AND coalesce( be.ban_end, unix_timestamp()+ 1 )>= unix_timestamp()
   AND (be.ban_email IS NULL OR be.ban_exclude<> 0)
 ORDER BY u.user_email
The NOW() from the post above is wrong when used arithmetically and in MySQL/MariaDB. Its intention was to exclude all members which haven't posted for more than 365 days (which doesn't honor those still visiting daily).
The worst thing about censorship is ███████████
Affin wrote:
Tue Nov 20, 2018 9:51 am
The problem is probably not my English but you do not want to understand correctly.
...
We will not come anybody anyway, nevertheless, it's best to shit this.

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 50426
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: phpmyadmin question

Post by stevemaury » Thu May 23, 2019 2:37 pm

I've been asked to create a list from the forum database of all active members' email addresses.
Banned users are not (necessarily) inactive. The <>2 eliminates inactive users.
The NOW() from the post above is wrong when used arithmetically and in MySQL/MariaDB.
How so?
Its intention was to exclude all members which haven't posted for more than 365 days (which doesn't honor those still visiting daily).
Which is what the OP asked for. Posters, not visitors:
I'd like to be able to make sure that only people who have logged in and posted in the last year get the email,
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)

User avatar
AmigoJack
Registered User
Posts: 5573
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン
Contact:

Re: phpmyadmin question

Post by AmigoJack » Thu May 23, 2019 4:08 pm

  1. Yes, but it doesn't eliminate ignored users. They can still have e-mail addresses. Or posts. Unrelated to why those may exist.
  2. Because it does so, just like the manual also tells. I even looked up if phpBB 3.2.7 now didn't store Unix timestamps anymore but uses another format, just to make sure. You should never find a NOW() in any SQL for phpBB - did you use that before?
  3. Touché - didn't even read that part. I wonder if sekellas really means "in the last year" (2018 in Gregorian) or wrongly synonymized the last 365 days.
The worst thing about censorship is ███████████
Affin wrote:
Tue Nov 20, 2018 9:51 am
The problem is probably not my English but you do not want to understand correctly.
...
We will not come anybody anyway, nevertheless, it's best to shit this.

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 50426
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: phpmyadmin question

Post by stevemaury » Thu May 23, 2019 4:28 pm

Thank you. In my 3.2.7 users table, Unix Timestamps appear to be being used:
Screen Shot 2019-05-23 at 11.30.43 AM.jpg
I looked at the manual link you gave, and see now that NOW() does not return a Unix Timestamp. I should have used UNIX_TIMESTAMP()

I assumed he meant 365 days prior to the running of the query.
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)

Post Reply

Return to “[3.2.x] Support Forum”