Query format to alter displaygroupid for members of usergroup?

[Tourmeister]

Howdy,

Here is what I have. I have a usergroup called "Forum Supporter". When members use the VB subscription feature to donate to the site, they are made "Additional Members" of the Forum Supporter usergroup for a period of 30 days. 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 Forum Supporter usergroup but I want to force their profile to identify them with the Forum Supporter 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:

http://dev.mysql.com/doc/mysql/en/index.html,

is pretty worthless in my opinion for someone trying to learn the basics. No, I am not a programmer. I am a pathetic user struggling to learn this stuff :p

Anyway, looking at the user table, I think I have figured out what needs to be done, just not specifically how to accomplish it.

Currently, for users that are Forum Supporters, I have these conditions:

usergroupid = 2 (Registered User)
membergrouids = 13 (Forum Supporter)
displaygroupid = 2

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

usergroupid = 2 (Registered User)
membergrouids = 13 (Forum Supporter)
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.

[Dismounted]

UPDATE `" . TABLE_PREFIX . "user`
SET `displaygroupid` = 13
WHERE `usergroupid` = 2
AND `membergroupids` = 13

[Tourmeister]

Thanks.

How would I incorporate that into a PHP script that I could run from my browser or the task scheduler in the Admin CP? Wouldn't it need to include the config.php or something to be sure that it could log into the database? Lastly, would the "TABLE_PREFIX" variable work within the PHP script file?

[Dismounted]

Thanks.

How would I incorporate that into a PHP script that I could run from my browser or the task scheduler in the Admin CP? Wouldn't it need to include the config.php or something to be sure that it could log into the database?
Yes, you will need to include global.php and use $vbulletin->db>query_write("QUERY");.
Lastly, would the "TABLE_PREFIX" variable work within the PHP script file?
It only works within PHP files that have global.php included.

[Tourmeister]

So would this be what I want for my PHP script? Also, would I place it in the /forums directory (that is where my forum site resides)


<?php

require_once('./global.php');

$vbulletin->db>query_write("

UPDATE `" . TABLE_PREFIX . "user`
SET `displaygroupid` = 13
WHERE `usergroupid` = 2
AND `membergroupids` = 13

");

?>

[Dismounted]

Yes.

[Tourmeister]

Hmmm.... Got this as the result on my test forum

Fatal error: Call to undefined function: query_write() in /home/vbscott/public_html/forums/update_fs_displaygroupid.php on line 5

using the file exactly as shown in my previous post. I am sure I am missing something really obvious... I have a disturbing knack for that :o

Ahhh.... as always...

There was no "-" after the $db in the line I cut/pasted from your post, hehe.

I should have looked closer. Anyway, I added it and the script seems to work perfectly. Thanks!

If I want to add this to my Scheduled Task Manager and place the script in the /includes/cron directory, can I leave out the this line:

require_once('./global.php');

since it is already in the cron.php file?

[Dismounted]

It doesn't matter (notice "once"), keep it in there just incase.

[Tourmeister]

Okay, thanks! The Forum Supporter members really like the change!