The behavior of Users table in mediawiki
When registering in a mediawiki installation, users are not obliged to give their names or emails, they just need to write a user and password. But if they decide to give name and email, they are willing to receive important updates in the website. Please do not use this data for anything else.
So you need to access the data in the users table, where emails could be empty, or duplicated, or even invalid. How to get a clean and useful list of name-email?
Conditions applied
We want the list to have:
- a name, preferibly the real name;
- the email, not duplicated, and valid as far as possible.
The SQL solution
SELECT
IF( TRIM( IFNULL( user_real_name, user_name ) ) = "", user_name, user_real_name ) AS u_name,
user_email AS u_email
FROM user
WHERE (user_email REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+.[A-Z]{2,4}$')
GROUP BY user_email
It returns two fields, u_name and u_email. First one (line 2) is taken from user_real_name, but if does not have a value, then user_name is chosen, as it is never empty.
The email is filtered through a regular expression (line 5). After that, duplicates are discarded grouping them (line 6). Voilà!
The mailing list of mediawiki users by geometrus, unless otherwise expressly stated, is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.