SQL Query

[Graham Smith]

We have a fairly standard xenForo installation.

We’re running off a shared server using PHP 7.0.28

Our database name is: ourforumname_xenforo

We have a few Custom Userfields:
Field ID: forename
Field ID: surname
Field ID: address
Field ID: membership_number

What I’ve been trying to do is download a .csv file which shows the following for all our registered users…

username, forename, surname, address, membership_number, primary usergroup

Any help would be much appreciated.

 

[S Thomas]

Do you have access to phpmyadmin, directly to the database (e.g. external db) and / or mysql on command line?

 

[Graham Smith]

Yes, I have access to phpmyadmin on the server.

We're running phpMyAdmin version 4.7.7

 

[S Thomas]

PMA -> select your db -> SQL -> query this

SELECT u.username as "username", GROUP_CONCAT((IF(f.field_value = '', '', f.field_value))) as "forename, surname, address, membership_number", u.user_group_id as "primary usergroup"
FROM xf_user_field_value f
RIGHT JOIN xf_user u ON (u.user_id = f.user_id)
WHERE f.field_id IN ('forename', 'surname', 'address', 'membership_number')
GROUP BY u.user_id

Scroll down to Query results operations -> Export ->
Export method: Custom
Format: CSV
Format-specific options:
Columns enclosed with and Columns escaped with:
(empty)
Check "Put columns names in the first row"

 

[Graham Smith]

That's great. Thanks so very much for this…

Unfortunately, some of our members have put commas in the 'address' field, and it screws the order of the Excel file up a bit.

I'm happy to remove the commas. How would I go about doing that?

 

[S Thomas]

Removing wouldn't make much sense, since new users still could mess up with the input fields. Instead, use something else to separate your columns, like "|".

SELECT u.username as "username", GROUP_CONCAT((IF(f.field_value = '', '', f.field_value)) SEPARATOR "|") as "forename, surname, address, membership_number", u.user_group_id as "primary usergroup"
FROM xf_user_field_value f
RIGHT JOIN xf_user u ON (u.user_id = f.user_id)
WHERE f.field_id IN ('forename', 'surname', 'address', 'membership_number')
GROUP BY u.user_id

And when exporting, make sure to change Columns separated with: from , to |

 

[Graham Smith]

You sir are an incredibly kind person. Thank you so very much for helping me with this. I'm now able to get the information I need to into Excel.

 

[Graham Smith]

Some time ago, a very kind user wrote a small SQL query for me as follows:

SELECT u.username as "username", GROUP_CONCAT((IF(f.field_value = '', '', f.field_value)) SEPARATOR "|") as "forename, surname, address, membership_number", u.user_group_id as "primary usergroup"
FROM xf_user_field_value f
RIGHT JOIN xf_user u ON (u.user_id = f.user_id)
WHERE f.field_id IN ('forename', 'surname', 'address', 'membership_number')
GROUP BY u.user_id

It basically downloads, username, custom field forename, custom field surname, custom field membership number and primary usergroup ID
It all works fine, but I now need to add any additional usergroups that they may (or may not) belong to.

Anyone able to help me add the additional code?