If you have access to run perl via cron and want to keep view counts while not having a row for every view, this perl might help.
Note: Use this at your own risk, I do not give any guarantees that it will work.
Code:
#!/usr/bin/perl -w
use DBI;
use DBD::mysql;
use strict;
my $db = 'db_name';
my $conn = ConnectToMySql($db);
my $query = '';
###############
#
# Compress public views
#
my $p_id = 0;
my $v_date = '';
my $v_count = 0;
my @data = '';
my $qry2 = '';
my $qry3 = '';
$query = "select profile_id, max(lastview), sum(viewscount) from PREFIX_comprofiler_views where viewer_id =0 group by profile_id";
my $viewrows = $conn->prepare($query);
$viewrows->execute();
while (@data = $viewrows->fetchrow_array()) {
$p_id = $data[0];
$v_date = $data[1];
$v_count = $data[2];
$qry2 = "delete from PREFIX_comprofiler_views where profile_id = $p_id and viewer_id =0";
my $delrows = $conn->prepare($qry2);
$delrows->execute();
$qry3 = "insert into PREFIX_comprofiler_views (viewer_id, profile_id, lastip, lastview, viewscount)
values (0, $p_id, '10.10.10.10', '$v_date', $v_count)";
my $insrow = $conn->prepare($qry3);
$insrow->execute();
}
###############
#
# The End
#
exit;
###############
#--- start sub ConnectToMySql -------------------
sub ConnectToMySql {
my ($db) = @_;
open(ACCESS_INFO, "</secure_directory/.file_containing_password") || die "Can't access login credentials";
my $host = 'localhost';
my $userid = 'db_user';
my $passwd = <ACCESS_INFO>;
my $connectionInfo="dbi:mysql:$db;$host";
close(ACCESS_INFO);
chomp ($host, $userid, $passwd);
my $l_connection = DBI->connect($connectionInfo,$userid,$passwd);
return $l_connection;
}
#--- end sub ConnectToMySql -------------------
krileon
