vb.org Archive - Remove old thread subscriptions

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB4 General Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=251)

- - Remove old thread subscriptions (https://vborg.vbsupport.ru/showthread.php?t=316153)

joeychgo

12-22-2014 05:38 AM

Remove old thread subscriptions

is there a query or something that could remove all thread subscriptions of users who haven't been on the forum for xxx amount of time?

kh99	12-22-2014 04:07 PM

Well, I strongly suggest that you have a backup before you try this, because I haven't tested it extensively. But first try this:

Code:

SELECT username, FROM_UNIXTIME(lastactivity) FROM user WHERE lastactivity < (UNIX_TIMESTAMP() - (86400 * X))

where you replace the red X with the number of days of inactivity. If this seems to select the right users, then you can do this:

Code:

DELETE FROM subscribethread WHERE userid IN (

      SELECT userid FROM user WHERE lastactivity < (UNIX_TIMESTAMP() - (86400 * X))

)

replacing the X again.

joeychgo

12-23-2014 07:50 AM

It "seems" to have worked.

186599 rows deleted. (Query took 14.3054 sec)

My subscriptions are still there... So I "think" it worked.

Thank you...

--------------- Added [DATE]1419328510[/DATE] at [TIME]1419328510[/TIME] ---------------

Is there a way to make this into a cron job so it can run automatically??

kh99	12-23-2014 10:02 AM

Quote:

Originally Posted by joeychgo (Post 2528427)

Is there a way to make this into a cron job so it can run automatically??

You could create a plugin using hook location cron_script_cleanup_daily and this code:

Code:

$vbulletin->db->query_write("DELETE FROM " . TABLE_PREFIX . "subscribethread 

                                                   WHERE userid IN (

                                                      SELECT userid FROM " . TABLE_PREFIX . "user 

                                                         WHERE lastactivity < (UNIX_TIMESTAMP() - (86400 * X))

)");

remember to change the X again. And you should probably test it by going to the scheduled task manager and pressing "Run Now" for the Daily Cleanup task. If it says "Daily Cleanup Done" with no error messages then you're good.

All times are GMT. The time now is 11:53 PM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01080 seconds Memory Usage 1,719KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (3)bbcode_code_printable (1)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (4)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: