How to find phpBB user that has custom field equal to '1234'?

Question

I have a phpBB installation. I've added custom profile field called 'insurance number'. Every user has different value there. I would like to get a user that has the value equal to '1234'.

How to get a user by the custom field?

Answer 1

First do:

SELECT * FROM phpbb_users LIMIT 1

To check the name of the custom field. It could be insurancenumber, insurance_number etc.

A query like this will select your data:

SELECT  u.user_id, u.username
FROM  phbb_users u
INNER JOIN phbb_profile_fields_data pf ON u.users_id = pf.users_id
WHERE u.`insurance number` = '1234'

Answer 2

Instead of running scripts to find the Custom profile fields, I simply used the phpBB admin GUI to get the field names. In phpBB3 admin GUI > Users and Groups tab > "Custom profile fields" in the Users section of the left nav.

The Custom profile field data is stored in the phpbb_profile_fields_data table. Columns are named using the Field identification with a "pf_" prefix. Our "company" field data is stored in pf_company column of the phpbb_profile_fields_data table.

SQL to get a list of usernames with a specific value in the "company" custom profile field. I used "Google" as an example:

SELECT u.username 
FROM phpbb_users u 
INNER JOIN phpbb_profile_fields_data pf ON u.user_id = pf.user_id 
WHERE pf.pf_company = 'Google'
ORDER BY u.username