Query to alter displaygroupid for members of additional usergroup?

[autoescala]

Hello to all,

Here is what I have. I have a usergroup called "VIP". When members use the VB subscription feature to donate to the site, they are made "Additional Members" of the Forum Supporter usergroup.

What I want is for these member's usernames to be green on the Who's Online List.

However, username markups only affect "Primary Members" of a usergroup. IF a particular member of the Forum Supporter usergroup happens to have enough snap to look in the User CP, they can choose to identify themselves with the Forum Supporter usergroup and then the markup works. The problem is, few even know about that feature, nor would they mess with it if I mentioned it.

I want to keep the users as Additional members of the Registered Users primary usergroup but I want to force their profile to identify them with VIP usergroup. The only way I can think to do this is to run a manual query on the "user" table. However, I cannot figure out the structure of the query and this site:

is pretty worthless in my opinion for someone trying to learn the basics. No, I am not a programmer.

Currently, for users that are VIP I have these conditions:

usergroupid = 2 (Registered User)
membergrouids = 13 (VIP)
displaygroupid = 2

and I want to change that for all Forum Supporters to the following:

usergroupid = 2 (Registered User)
membergrouids = 13 (VIP)
displaygroupid = 13

It is crucial that the query check to ensure that the usergroupid = 2 AND membergroupids = 13 before making the update to usergroupid because there are some other usergroups that might get selected and I don't want to mess with their displaygroupid.

I would really like to have this as a PHP script so that I can add it to the Scheduled Task manager to run once a day for new members of the Forum Supporter usergroup. It would be sweet if it could be done via the subscription creation, but there is no option for controlling the displaygroupid that I know of.

I would really appreciate anyone being able to help me out.

Best Regards

[kh99]

I think this might work (although I haven't tried it):

PHP Code:

$vbulletin->db->query_write("UPDATE " . TABLE_PREFIX . "user set displaygroupid=13
                                              WHERE usergroupid=2 AND FIND_IN_SET(13, membergroupids)"); 


I think you could also set it when the subscription happens by editing the file includes/class_paid_subscription.php. Around line 397 (in version 4.2.2) is this code:

if (!empty($sub['membergroupids']))
{
	$membergroupids = array_merge(fetch_membergroupids_array($user, false), array_diff(fetch_membergroupids_array($sub, false), fetch_membergroupids_array($user, false)));
	if ($user['usergroupid'] == 2 AND in_array(13, $membergroupids))
	{
		$userdm->set('displaygroupid', 13);
	}
}

and I think if you add the part in red it might set the displaygroupid for you (again I haven't tested it). Also, I'm not sure what will happen when the subscription expires, there may be other code needed to remove it.


ETA: I guess the above could be done using a hook on location paidsub_build, but it would require a little more code since the $userdm has been destroyed at that point. But maybe someone can work out the code for that to avoid editing files.

[autoescala]

Sorry, but i copy/paste your code in Manual Queery field to execute and it says:

error 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$vbulletin->db->query_write("UPDATE " . TABLE_PREFIX . "user set displaygroupid=' at line 1

I'm doing something wrong ? your code can't be used from the Admin Cpanel?

Best Regards and Thanks for your time

[kh99]

Well, you said you wanted php code. If you just want the query it would be something like:

UPDATE user set displaygroupid=13 WHERE usergroupid=2 AND FIND_IN_SET(13, membergroupids)

...but if you have a table prefix, you'll have to add that before "user". (Try it first, if it doesn't work because of an unknown table, then you might need a prefix).

[autoescala]

Originally Posted by kh99

Well, you said you wanted php code. If you just want the query it would be something like:

UPDATE user set displaygroupid=13 WHERE usergroupid=2 AND FIND_IN_SET(13, membergroupids)

...but if you have a table prefix, you'll have to add that before "user". (Try it first, if it doesn't work because of an unknown table, then you might need a prefix).

IT WORKS!!! Thank you very much!

[autoescala]

@kh99 your tip works as a charm. But how can I do to automatize this as a cron job?

I created the first php based on first answers, but If I load from a browser nothing happens, I thing. Its normal ?

Thanks in advance

[kh99]

I'm not sure what you mean exactly, about loading from a browser. But if you want it to run automatically, create a plugin using your code (or my code from post #2) and use hook location cron_script_cleanup_daily or cron_script_cleanup_hourly, depending on how often you want it to run.

[autoescala]

Originally Posted by kh99

I'm not sure what you mean exactly, about loading from a browser. But if you want it to run automatically, create a plugin using your code (or my code from post #2) and use hook location cron_script_cleanup_daily or cron_script_cleanup_hourly, depending on how often you want it to run.

thank you very much, very useful your answer, I'm able to apply your solution easily, and works fine!!!
Best Regards