How to update User Titles and Ranks automatically?

[TheLastSuperman]

Originally Posted by gsk8

I would also like to update the User Titles via a scheduled task. VB can't help and sent me here.

I have a hack that auto-bans spammers, but I still have to update the counters manually in order for the "banned" to show in the User Title.....

Been trying to figure this out off and on for a few years

Originally Posted by Smitty

Same here. And running the update manually with over 250,000 registered people takes a *long* time.

What mod? Please link me to it .

For now try editing a cron job and by that I mean the file for instance one related to a scheduled task that runs at least daily. Where can you add the code? Ensure your scheduled tasks are enabled then edit the file cleanup.php OR cleanup2.php OR dailycleanup.php which is located in the /includes/cron/ folder and add the query just below:

PHP Code:

// ########################################################################
// ######################### START MAIN SCRIPT ############################
// ######################################################################## 


PHP Code:

// Update usertitles for banned members
$vbulletin->db->query_write("UPDATE " . TABLE_PREFIX . "user
SET usertitle = 'Banned'
WHERE usergroupid = '8' AND usertitle != 'Banned'
");

// Update user ranks to none for banned members
$vbulletin->db->query_write("UPDATE " . TABLE_PREFIX . "user AS user
LEFT JOIN " . TABLE_PREFIX . "usertextfield AS usertextfield
ON user.userid = usertextfield.userid
SET usertextfield.rank = NULL
WHERE usergroupid = '8'
"); 


If you utilize cleanup.php, its the file ran when the Hourly Cleanup scheduled task runs, so with that being said the username update would take place every hour. If you have quite a few members as some noted, you may wish to do this once per day and would then instead edit the file dailycleanup.php instead, now the user title update takes place once per day - less queries and less often unless updating something live is a good rule of thumb.

Enjoy!


Note: Should delete entries in ranks table using query similar to the below as well HOWEVER this sets usertitle to guest and removes user info from post and requires fix broken user profiles to be used so just an example query for now until I have time to adjust it properly i.e. insert into instead of delete:

PHP Code:

// Delete the entry in ranks table for the banned members
$vbulletin->db->query_write("DELETE " . TABLE_PREFIX . "usertextfield
FROM " . TABLE_PREFIX . "usertextfield LEFT JOIN " . TABLE_PREFIX . "user
ON usertextfield.userid = user.userid
WHERE usertextfield.rank IS NULL
"); 


Edit: In all honesty using the data manager via a custom script would be best in a situation like this versus queries running via cron jobs just so your aware, the above queries will work though (don't use the bottom one, that deletes entries it was scratch code but works just throws the data manager for a loop per say).