So just following up If you do take the approach of doing it totally outside of Xenforo with say a script running every so often via cron.
You'll want an SQL query to get the users who you want to place into your patron usergroup. So firstly identify the DB ID of the upgrade. You can find that just by looking at the URLs in the
user upgrades part of the control panel -
/admin.php?user-upgrades/upgrade-name-here.2/edit for instance has an ID of
2. So lets assume your upgrade has an ID of 2 and the
patron group has an ID of 30 for the sake of an example:
SQL:
select xf_user_upgrade_active.user_id, xf_user.username, xf_user.secondary_group_ids
from xf_user_upgrade_active left join xf_user on xf_user_upgrade_active.user_id = xf_user.user_id
where xf_user_upgrade_active.user_upgrade_id in (2) and !find_in_set(30, xf_user.secondary_group_ids);
So we're looking at the active upgrades table - if they have bought your upgrade (ID 2) we'll find them in this table. Now I think you've probably got two upgrade IDs - Premium and VIP so I've done the query so you could do both (rather than a straight
a = b) - since we have a where condition which is "
where value is in this list" -
where xf_user_upgrade_active.user_upgrade_id in (2) that could be
2,8 for instance if 2 and 8 were the upgrade IDs.
However we're probably not interested in users who are already in the Patron group (30 in this example) so we've got a extra condition to exclude anyone who's secondary group field contains that entry -
and !find_in_set(30, xf_user.secondary_group_ids).
We do have to join in the
xf_user table since we need access to those secondary groups, especially if we might then update them!
Anyhow that would give you the:
user_id,
username and
secondary_group_ids for each user who has an active
Premium or
VIP upgrade, but is not in the
Patron group.
Armed with this information you could then use the
XenForo API (also
read this) to update the secondary user groups of those users to include the
Patron group by making a POST query to
users/<user_id>/ for each user (
<user_id> being the user's ID) and passing in
secondary_group_ids[]=<full_list_of_groups_for_user> as the parameter (
<full_list_of_groups_for_user> being their existing list of groups plus the extra patron one). Where possible I think it best to use the API to update things like this as it ensure any special XF logic is applied rather than just blindly tweaking the database (note the secondary group field in the DB is a binary field not just a plain string of comma separated values).
Anyhow that's how I'd do it. Now of course if you are happy with PHP and the XF framework you could do all that in an add-on or something probably using the various XF classes and be much neater about the whole affair. Alas that's beyond my pay grade.
If I were hacking this as a shell script to run periodically to add "upgraded" users into the Patron group I would be doing (untested) something like:
Bash:
#!/bin/sh
# MySQL command line to get our users, but we don't want all the fancy table markup or column names, just user IDs and groups please
# we'll pipe that directly into a while loop to read each line of output...
mysql xenforo_database_name -e 'select xf_user_upgrade_active.user_id, xf_user.secondary_group_ids from xf_user_upgrade_active
left join xf_user on xf_user_upgrade_active.user_id = xf_user.user_id
where xf_user_upgrade_active.user_upgrade_id in (2) and !find_in_set(30, xf_user.secondary_group_ids);' --batch --skip-column-names |
while read line
do
# Extract the user ID and existing groups out from the line
user_id=$(echo $line | cut -f1 -d ' ')
user_groups=$(echo $line | cut -f2 -d ' ')
# Create a new string with the extra group added on,
# in this example the extra group is group 99
new_user_groups="$user_groups,99"
# Echo things back to the screen so we can see what we've set
echo "Line: $line"
echo "User: $user_id"
echo "Existing Groups: $user_groups"
echo "Desired Groups: $new_user_groups"
# Iterate over the list of groups we want and construct the curl options we'd need to set these groups
options=$(echo $new_user_groups | awk -F "," '{for(i=1; i<=NF; i++) { printf " --data-urlencode %s", $i; }}')
# Echo our curl payload and curl command to the screen
echo "API payload: $options"
echo "Curl Command: curl --header 'XF-Api-Key: secret-api-key' --header 'XF-Api-User: 1' --request POST 'https://forum.example.com/api/users/$user_id/' $options"
echo
# TODO actually execute the curl command to update the user's groups!
done
Obviously (I hope) putting in correct values for the XF database, API key, URL, user_upgrade_id, existing group and new group ID (99 in my script example above).
Note in that example I've not actually executed the curl command because I don't want anyone blindly copying and pasting without understanding what they are doing, partially also why it's quite verbose. I'd personally be using a more fully featured programming language, be that PHP, or whatever still I thought it might be interesting as a thought exercise to do it as a nice vanilla bourne shell script (other shells will no doubt have different escaping quirks), maybe it'll serve as a starting point, but you'd be better off and more robust reading the database and so forth using a proper library rather than hacking away at the output if you do go via this route.
![[XB] Badges](/community/data/resource_icons/8/8456.jpg?1749071507){kind=icon}
