[PARTIAL FIX] Index Robot counts in video views

THS · 2012-03-07 07:16:13

I was trying to figure out why the views where so high when i knew traffic was low. After going through the video_history table and seeing a pattern in the ip strings, I finally converted them to see it was the indexing spiders from google registering. Is there a way to filter by IP or by agent to insure we are only counting valid human views?

symtab · 2012-03-07 08:10:24

Go to modules/video/components/view.php and find this line:

            $this->db->query("UPDATE #__video
                              SET total_views = total_views+1,
                                  view_date = '".$date."',
                                  view_time = ".time()."
                              WHERE video_id = ".$this->video_id." LIMIT 1");

replace with:

if (!VBrowser::get('is_robot')) {
            $this->db->query("UPDATE #__video
                              SET total_views = total_views+1,
                                  view_date = '".$date."',
                                  view_time = ".time()."
                              WHERE video_id = ".$this->video_id." LIMIT 1");
}

flogger123 · 2012-03-07 16:42:12

So just add this line?

if (!VBrowser::get('is_robot')) {

I'll try to test this.

Eri · 2012-03-07 16:50:11

I think this must be added at next version will reduce SQL load.

THS · 2012-03-07 19:21:22

Symtab,

Why would we not be wrapping all 3 queries right there? That would reduce even more calls to the db. For clariification for others:

if (!VBrowser::get('is_robot')) {
			$this->db->query("UPDATE #__video
			                  SET total_views = total_views+1,
			                      view_date = '".$date."',
			                      view_time = ".time()."
			                  WHERE video_id = ".$this->video_id." LIMIT 1");
			$this->db->query("UPDATE #__user_activity SET total_video_views = total_video_views+1 WHERE user_id = ".$owner_id." LIMIT 1");
			$this->db->query("INSERT INTO #__video_history
			                  SET video_id = ".$this->video_id.",
			                      user_id = ".$user_id.",
			                      ip = ".$ip.",
			                      view_date = '".$date."'");
			}

symtab · 2012-03-08 07:21:28

Ok. Will do this. I thaught that a busy site is better, but a faster site is even better :-)

THS · 2012-03-13 02:07:58

Symtab, this hasn't resolved the issue. I thought it did as traffic initially dropped. For some reason I had Google hit me 4826 times in the last 5 hours. Although the busy site looks nice, it is impacting site performance (I would assume as that is an additional almost 15K queries to the DB for no reason). Do you think there is something I missed?

symtab · 2012-03-13 07:59:48

Hmmm...so even after you added the !VBrowser::get('is_robot') line, the video views are still update for robots? If will check my browser class and robots config file.

Eri · 2012-03-13 09:35:04

I had the same problem my database after 1 month was 2 GB so i have removed the

$this->db->query("INSERT INTO #__video_history
   SET video_id = ".$this->video_id.",
   user_id = ".$user_id.",
   ip = ".$ip.",
   view_date = '".$date."'");
}

and my database is 70MB

symtab · 2012-03-13 12:01:31

I know that. The video history is removed from 1.0 (i changed it and with cron you can keep the daily statistics, and also have a small db).

THS · 2012-03-13 13:34:52

Ok, I will do the same.

symtab · 2012-03-20 12:23:18

@THS: even after the !VBrowser:get('is_robot') line, it still counts robots?

THS · 2012-03-20 22:54:40

Unfortunately, yes. The long ip with the most daily hits comes back to google directly.

symtab · 2012-03-21 07:02:02

Hmm...in access_log can you see its a robot?

THS · 2012-03-21 11:46:41

Where is this one located? My server logs are stuffed with so many requests its not possible to tell whats what anymore. Apologies.

symtab · 2012-03-21 15:45:50

Its the apache access log. I'm worried that the script does not detect the robot, there must be a error somewhere I'll retest this before 1.0.

THS · 2012-03-21 20:22:20

I have a way of confirming this (I hope via the logs). I will check shortly.

symtab · 2012-05-15 11:39:01

I was able to only add the views update on non robot visitors. I will mark this a partially fixed, and once i test this in the wild i will mark it as fixed.

Eri · 2012-05-15 11:46:40

why should add view_date and view_time at query.
At frontage needs only view_time

symtab · 2012-05-15 11:53:14

Its a design problem that remained from the first version of the script. I will check and find if view_date is used and if its not used. I will remove it.

UPDATE: removed from the video view component.

Adult Script Pro Community Forums

#1 2012-03-07 07:16:13

[PARTIAL FIX] Index Robot counts in video views

#2 2012-03-07 08:10:24

Re: [PARTIAL FIX] Index Robot counts in video views

#3 2012-03-07 16:42:12

Re: [PARTIAL FIX] Index Robot counts in video views

#4 2012-03-07 16:50:11

Re: [PARTIAL FIX] Index Robot counts in video views

#5 2012-03-07 19:21:22

Re: [PARTIAL FIX] Index Robot counts in video views

#6 2012-03-08 07:21:28

Re: [PARTIAL FIX] Index Robot counts in video views

#7 2012-03-13 02:07:58

Re: [PARTIAL FIX] Index Robot counts in video views

#8 2012-03-13 07:59:48

Re: [PARTIAL FIX] Index Robot counts in video views

#9 2012-03-13 09:35:04

Re: [PARTIAL FIX] Index Robot counts in video views

#10 2012-03-13 12:01:31

Re: [PARTIAL FIX] Index Robot counts in video views

#11 2012-03-13 13:34:52

Re: [PARTIAL FIX] Index Robot counts in video views

#12 2012-03-20 12:23:18

Re: [PARTIAL FIX] Index Robot counts in video views

#13 2012-03-20 22:54:40

Re: [PARTIAL FIX] Index Robot counts in video views

#14 2012-03-21 07:02:02

Re: [PARTIAL FIX] Index Robot counts in video views

#15 2012-03-21 11:46:41

Re: [PARTIAL FIX] Index Robot counts in video views

#16 2012-03-21 15:45:50

Re: [PARTIAL FIX] Index Robot counts in video views

#17 2012-03-21 20:22:20

Re: [PARTIAL FIX] Index Robot counts in video views

#18 2012-05-15 11:39:01

Re: [PARTIAL FIX] Index Robot counts in video views

#19 2012-05-15 11:46:40

Re: [PARTIAL FIX] Index Robot counts in video views

#20 2012-05-15 11:53:14

Re: [PARTIAL FIX] Index Robot counts in video views

Board footer