Members by location

Need some custom code changes to the phpBB core simple enough that you feel doesn't require an extension? Then post your request here so that community members can provide some assistance.

NOTE: NO OFFICIAL SUPPORT IS PROVIDED IN THIS SUB-FORUM
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

NOTE: NO OFFICIAL SUPPORT IS PROVIDED IN THIS SUB-FORUM
Post Reply
kronos1
Registered User
Posts: 556
Joined: Tue Aug 23, 2011 1:46 pm

Members by location

Post by kronos1 »

Is there away you can list or search members by their location or does anyone have any SQL code for this so I can run it in phpMyAdmin.

Thank you.
User avatar
warmweer
Jr. Extension Validator
Posts: 11242
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Bel ... gium
Contact:

Re: Members by location

Post by warmweer »

kronos1 wrote: Mon Jan 07, 2019 8:17 pm Is there away you can list or search members by their location or does anyone have any SQL code for this so I can run it in phpMyAdmin.

Thank you.
Since you can use phpMyAdmin, why not use the in-built querybuilder? There's even a search function ready (with wildcards). You'll get the correct SQL somewhere near the top of the page.
Spelling is freeware, which means you can use it for free.
On the other hand, it is not open source, which means you cannot change it or publish it in a modified form.


Time flies like an arrow, but fruit flies like a banana.
kronos1
Registered User
Posts: 556
Joined: Tue Aug 23, 2011 1:46 pm

Re: Members by location

Post by kronos1 »

Thank you for that, this is the first time I have used the in-built querybuilder.

I am using the following which lists all the users locations but all the users names come up as anonymous. What am I doing wrong.

Code: Select all

SELECT `phpbb_profile_fields_data`.`pf_phpbb_location`, `phpbb_users`.`username`
FROM `phpbb_profile_fields_data`, `phpbb_users`;
User avatar
warmweer
Jr. Extension Validator
Posts: 11242
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Bel ... gium
Contact:

Re: Members by location

Post by warmweer »

Username is in the users_table and should be found by joining to user_id from phpbb_ profile_fields_data to user_id from phpbb_users
I can't write correct SQL with joins just like that (too used to VB-sql) but you should check non-empty locations

something like FROM 'phpbb_profile_fields_data'.'user_id' WHERE 'pf_phpbb_location' != ''

If you're used to working with MS Office, you can use an odbc connector and do everything in Access (the experts will probably cringe reading this, but hey, it does the job for me).
Spelling is freeware, which means you can use it for free.
On the other hand, it is not open source, which means you cannot change it or publish it in a modified form.


Time flies like an arrow, but fruit flies like a banana.
kronos1
Registered User
Posts: 556
Joined: Tue Aug 23, 2011 1:46 pm

Re: Members by location

Post by kronos1 »

Thank you for the help Warmweer but is beyond me to be able to work that out, hopefully someone who programs in SQL will come along and help.
Post Reply

Return to “phpBB Custom Coding”