A query to pull just Registered Users email from Users Table

[caliman]

Hi all - thanks in advance... I was curious if anyone could save me a bit of time from tracking down all the normalization...


I am looking for a query to pull just Registered Users email from Users Table.

Any help is greatly appreciated.

caliman

[Eikinskjaldi]

Depends on your definition of "registered user". Do you mean just member group 2, or do you want to include admins or other custom usergrops?

select username, email from user where usergroupid in (list,of,allowed,groups)

[caliman]

Thank you!
I meant just 2, but I hadn't actually thought about me or my mods.

--------------- Added at 21:03 ---------------

Ok... I admit I was being lazy... I looked all this up... in addition to what you stated as the query, here are the actual groups.. perhaps this will help other lazy people.

mysql> desc usergroups;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| groupid    | int(5)      |      | PRI | NULL    | auto_increment |
| groupname  | varchar(50) |      |     |         |                |
| cpaccess   | smallint(3) |      |     | 0       |                |
| modaccess  | smallint(3) |      |     | 0       |                |
| uploads    | smallint(3) |      |     | 1       |                |
| comments   | smallint(3) |      |     | 1       |                |
| diskspace  | int(10)     | YES  |     | NULL    |                |
| uploadsize | int(10)     | YES  |     | NULL    |                |
| uplimit    | int(5)      |      |     | 0       |                |
| editpho    | int(3)      |      |     | 0       |                |
| editposts  | int(3)      |      |     | 0       |                |
| useralbums | int(3)      |      |     | 0       |                |
| reqmod     | int(3)      |      |     | 0       |                |
| canassign  | int(3)      |      |     | 0       |                |
+------------+-------------+------+-----+---------+----------------+
14 rows in set (0.02 sec)

mysql> SELECT * FROM usergroups;
+---------+-----------------------------------+----------+-----------+---------+----------+-----------+------------+---------+---------+-----------+------------+--------+-----------+
| groupid | groupname                         | cpaccess | modaccess | uploads | comments | diskspace | uploadsize | uplimit | editpho | editposts | useralbums | reqmod | canassign |
+---------+-----------------------------------+----------+-----------+---------+----------+-----------+------------+---------+---------+-----------+------------+--------+-----------+
|       1 | Unregistered / Not Logged In      |        0 |         0 |       0 |        0 |         0 |          0 |       0 |       0 |         0 |          0 |      0 |         0 |
|       2 | Registered Users                  |        0 |         0 |       1 |        1 |     50000 |       1000 |      20 |       1 |         1 |          1 |      0 |         0 |
|       3 | Users Awaiting Email Confirmation |        0 |         0 |       0 |        0 |         0 |          0 |       0 |       0 |         0 |          0 |      0 |         0 |
|       4 | (COPPA) Users Awaiting Moderation |        0 |         0 |       0 |        0 |         0 |          0 |       0 |       0 |         0 |          0 |      0 |         0 |
|       5 | Super Moderators                  |        0 |         0 |       0 |        0 |         0 |          0 |       0 |       0 |         0 |          0 |      0 |         0 |
|       6 | Administrators                    |        1 |         0 |       1 |        1 |         0 |          0 |       0 |       1 |         1 |          1 |      0 |         1 |
|       7 | Moderators                        |        0 |         0 |       0 |        0 |         0 |          0 |       0 |       0 |         0 |          0 |      0 |         0 |
|       8 | Banned Users                      |        0 |         0 |       0 |        0 |         0 |          0 |       0 |       0 |         0 |          0 |      0 |         0 |
|      (edited)    | (Your site) Moderator                   |        0 |         1 |       1 |        1 |     50000 |      20000 |      20 |       1 |         1 |          1 |      0 |         0 |
+---------+-----------------------------------+----------+-----------+---------+----------+-----------+------------+---------+---------+-----------+------------+--------+-----------+
9 rows in set (0.01 sec)

mysql>

May be slightly hard to read but you should see the groupids rather easily.

caliman

[Eikinskjaldi]

Actually on my board I have a handful of extra custom groups, which is why I asked the question

[caliman]

Aye. I hadn't thought of that either. lol.

This wine is good.

[Marco van Herwaarden]

If you are really being lazy and only want the email addresses:

AdminCP -> Users -> Generate Mailing List

[caliman]

Nice Marco!