XF 2.1 Need help with query, thanks

[beerForo]

I have a user group called Expired I want to delete. It is a legacy group from another software but is expired user upgrades.

I want to create a custom field only admins can see, let's call it Expired Legacy, and, those in that group now, mark as Yes in this field. Then I will delete it but I would like to keep the information. Thanks!

 

[beerForo]

Would anyone be able to assist? Thanks!

 

[bzcomputers]

I have a user group called Expired I want to delete. It is a legacy group from another software but is expired user upgrades.

I want to create a custom field only admins can see, let's call it Expired Legacy, and, those in that group now, mark as Yes in this field. Then I will delete it but I would like to keep the information. Thanks!

You would first want to install this add-on: https://xenforo.com/community/resources/custom-fields-permissions.6108/

This will give you the admin only viewing permission control for custom fields you are looking for which is not available in a stock XenForo install.

Then go ahead and create a custom user field: AdminCP - Users - Custom User Field. Set Field ID to "expired_legacy" and set your Title and description to whatever you'd like. After that the only other thing you'd need to set for the custom user field is the view permissions that will only be available if you have already installed the add-on linked above. You can always go back and install the add-on and make the permission changes after.

To first make sure you are identifying the correct users that you want to add the custom field for, test with this query:

SELECT username FROM xf_user WHERE secondary_group_ids LIKE('%X%')

This will give you a list of users whose secondary_user_group includes "X" - adjust the "X" to whatever your Expired group id is (you can find this in the xf_user_group table if you do not know it).

Now that you know you have identified the right users, you can go ahead and add the custom field for them with this query.

INSERT INTO xf_user_field_value (user_id, field_id, field_value) SELECT user_id, 'expired_legacy', 'yes' FROM xf_user WHERE secondary_group_ids LIKE('%X%')

Again, you need to adjust the "X" to whatever the secondary group id is for your Expired users.

That should do it.

 

[beerForo]

Fantastic, thanks so much!

 

[beerForo]

@bzcomputers I have decided to make this a checkbox field.
Would you be able to help with the query? Would it still be the same?
So Legacy Membership is the title
Options:
value = yes
text = Yes

Thanks!

 

[bzcomputers]

I am assuming you have already run the initial queries and now have the data inserted into the "xf_user_field_value" table and now you just need a query to retrieve the data and display it as a checkbox?

 

[beerForo]

No I didn't yet.

 

[bzcomputers]

The query would be the same.

Then to display the data as a checkbox you would just need to assign the 'yes' value as 'checked'.

 

[beerForo]

Where would we assign that?

 

[bzcomputers]

Right where you want the checkbox displayed.

 

[beerForo]

Thanks for all your help, field created, group removed, cleaner and can still find and contact the legacy users with a check box.