I noticed in your "who viewed me" mod it doesn't include a cron job or function to purge old data.
I know it might not be mandatory but people should consider purging data older than a set time frame.
Also, consider that when a user deletes their account perhaps delete the stored WVM data which relates to that user to help keep things upto date.
KHDev, I created a purge_who_viewed_me option just like I did for the spambox. I set it for 1 day in the admin panel about 35 minutes ago and will see what happens when the one day period completes it's cycle. To share what I did, here are the instructions. If anyone finds an error in my code, please mention it.
#1
in your database dsb_site_options3, run this line in SQL
INSERT INTO `dsb_site_options3` (`config_option`, `config_value`, `config_diz`, `option_type`, `choices`, `fk_module_code`, `per_user`) VALUES ('purge_who_viewed_me', '', 'Purge old whos viewed me data after how many days? (0 for never)', '104', '', 'core', '0');#2
go to yoursiteroot.com/tools/cron/jobs/d and open 1purges.php
replace this code with what you have in this file.
<?php
$jobs[]='purges';
function purges() {
global $dbtable_prefix;
$now=gmdate('YmdHis');
$config=get_site_option(array('purge_unverified','purge_inbox','purge_trash','purge_folders','purge_outbox','purge_spambox', 'purge_who_viewed_me'),'core'); //added purge_who_viewed_me - by Marble 9-04-2015
// these are orphaned accounts, not activated and with no profile data. These should not exist unless a critical error occured during join.
$query="SELECT `".USER_ACCOUNT_ID."` FROM `".USER_ACCOUNTS_TABLE."` a LEFT JOIN `{$dbtable_prefix}user_profiles` b ON a.`".USER_ACCOUNT_ID."`=b.`fk_user_id` WHERE a.`status`=".ASTAT_UNVERIFIED." AND b.`fk_user_id` IS NULL";
if (!($res=@mysql_query($query))) {trigger_error(mysql_error(),E_USER_ERROR);}
$to_del=array();
for ($i=0;$i<mysql_num_rows($res);++$i) {
$to_del[]=mysql_result($res,$i,0);
}
if (!empty($to_del)) {
$query="DELETE FROM `".USER_ACCOUNTS_TABLE."` WHERE `".USER_ACCOUNT_ID."` IN ('".join("','",$to_del)."')";
if (!($res=@mysql_query($query))) {trigger_error(mysql_error(),E_USER_ERROR);}
}
// these are orphaned accounts, with profile data but not activated. We cannot delete them but we can't let them like this. We need a profile for them
$query="SELECT `".USER_ACCOUNT_ID."` FROM `".USER_ACCOUNTS_TABLE."` a LEFT JOIN `{$dbtable_prefix}user_profiles` b ON a.`".USER_ACCOUNT_ID."`=b.`fk_user_id` WHERE a.`status`=".ASTAT_ACTIVE." AND b.`fk_user_id` IS NULL";
if (!($res=@mysql_query($query))) {trigger_error(mysql_error(),E_USER_ERROR);}
$to_profile=array();
for ($i=0;$i<mysql_num_rows($res);++$i) {
$to_profile[]=mysql_result($res,$i,0);
}
if (!empty($to_profile)) {
if (get_site_option('manual_profile_approval','core')==1) {
$pstat=STAT_PENDING;
} else {
$pstat=STAT_APPROVED;
}
$query="INSERT INTO `{$dbtable_prefix}user_profiles` (`fk_user_id`,`status`,`date_added`,`_user`) SELECT `".USER_ACCOUNT_ID."`,$pstat,'$now',`".USER_ACCOUNT_USER."` FROM `".USER_ACCOUNTS_TABLE."` WHERE `".USER_ACCOUNT_ID."` IN ('".join("','",$to_profile)."')";
if (!($res=@mysql_query($query))) {trigger_error(mysql_error(),E_USER_ERROR);}
}
if (!empty($config['purge_unverified'])) {
$query="SELECT `".USER_ACCOUNT_ID."` FROM `".USER_ACCOUNTS_TABLE."` a,`{$dbtable_prefix}user_profiles` b WHERE a.`".USER_ACCOUNT_ID."`=b.`fk_user_id` AND a.`status`=".ASTAT_UNVERIFIED." AND b.`date_added`<DATE_SUB('$now',INTERVAL ".$config['purge_unverified']." DAY)";
if (!($res=@mysql_query($query))) {trigger_error(mysql_error(),E_USER_ERROR);}
$to_del=array();
for ($i=0;$i<mysql_num_rows($res);++$i) {
$to_del[]=mysql_result($res,$i,0);
}
if (!empty($to_del)) {
$query="UPDATE `{$dbtable_prefix}user_profiles` SET `del`=1 WHERE `fk_user_id` IN ('".join("','",$to_del)."')";
if (!($res=@mysql_query($query))) {trigger_error(mysql_error(),E_USER_ERROR);}
}
}
if (!empty($config['purge_inbox'])) {
$query="DELETE FROM `{$dbtable_prefix}user_inbox` WHERE `date_sent`<DATE_SUB('$now',INTERVAL ".$config['purge_inbox']." DAY) AND `del`=0 AND `fk_folder_id`=0";
if (!($res=@mysql_query($query))) {trigger_error(mysql_error(),E_USER_ERROR);}
}
if (!empty($config['purge_trash'])) {
$query="DELETE FROM `{$dbtable_prefix}user_inbox` WHERE `date_sent`<DATE_SUB('$now',INTERVAL ".$config['purge_trash']." DAY) AND `del`=1";
if (!($res=@mysql_query($query))) {trigger_error(mysql_error(),E_USER_ERROR);}
}
if (!empty($config['purge_folders'])) {
$query="DELETE FROM `{$dbtable_prefix}user_inbox` WHERE `date_sent`<DATE_SUB('$now',INTERVAL ".$config['purge_folders']." DAY) AND `del`=0 AND `fk_folder_id`<>0";
if (!($res=@mysql_query($query))) {trigger_error(mysql_error(),E_USER_ERROR);}
}
if (!empty($config['purge_outbox'])) {
$query="DELETE FROM `{$dbtable_prefix}user_outbox` WHERE `date_sent`<DATE_SUB('$now',INTERVAL ".$config['purge_outbox']." DAY)";
if (!($res=@mysql_query($query))) {trigger_error(mysql_error(),E_USER_ERROR);}
}
// allow member spam boxes to be purged automatically - Marble from Datemill
if (!empty($config['purge_spambox'])) {
$query="DELETE FROM `{$dbtable_prefix}user_spambox` WHERE `date_sent`<DATE_SUB('$now',INTERVAL ".$config['purge_spambox']." DAY)";
if (!($res=@mysql_query($query))) {trigger_error(mysql_error(),E_USER_ERROR);}
}
//allow who viewed me data to be purged automatically - by Marble 9-04-2015
if (!empty($config['purge_who_viewed_me'])) {
$query="DELETE FROM `{$dbtable_prefix}who_viewed_me` WHERE `date`<DATE_SUB('$now',INTERVAL ".$config['purge_who_viewed_me']." DAY)";
if (!($res=@mysql_query($query))) {trigger_error(mysql_error(),E_USER_ERROR);}
}
return true;
}The changes I made are on lines #8 and #79
#3
go to your Admin Control Panel > General Settings > Features and Options and set a number value in the text box for after how many days do you want to wait for the data to be purged
I don't know how to delete the stored WVM data. Does anyone else know?
***************************
first error of the day. I made a little mistake and corrected it in the above code. I changed in line #81 it was written with
WHERE `date_sent` . The error indicated that this column didn't exist, which was correct. I changed it to
WHERE `date`.
