Tracking page views, calculate hours between 2 mysql datetime fields

[JoeAtRevolutionUnltd]

I'm trying to track page views.

I am storing the ip, date_viewed, and total_views. I want to check the ip and if it is the same as the last stored, check that the last visit was more than an hour ago, and if so then update the total page views.

Can anyone point me in the right direction?

 

[jpadie]

i don't really understand the logic. but date manipulations are best done in mysql. Include the conditionality in a where clause. see

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

for the various functions available to you.

alternatively, if you are not using datetime/timestamp fields (or similar) to store the timestamp, but using a float or integer field and storing the time as a unix timestamp, then you could use php to calculate the time difference.

 

[JoeAtRevolutionUnltd]

I'll try to explain myself a little better.

I'm storing news articles in a table with an id, title, text, etc. I want to be able to track views of each item, but also not allow the user to refresh the page 5 times and add 5 views. So I added an ip, date_viewed, and total_views fields to the table and when the function to get that article is called I want to get the users IP and if it is the same as the stored ip, check the date viewed, and if that date is more than an hour ago, update the total views count.

Does that help a little? Is there a better way to go about this?

 

[jpadie]

i don't think that IP address is a great way to track people. it's too variable. particularly with DSL/dial up connections. and it's possible that you would not be logging viable hits from behind a corporate firewall.

anywhere here is a quick class to do what you want

<?php
new logger();
class logger{
	private $totalVisits = '';
	private $lastVisit = '';
	private $ip = '';
	private $tableName = '';
	
	public function __construct(){
		$this->populateIP();
		$this->getVisitorData();
		$this->logVisit();
	}
	
	private function getLastVisit(){
		$sql = "Select lastVisit, totalVisits from {$this->tableName} where ipAddress='%s'";
		$result = mysql_query(sprintf($sql, $this->ip));
		$row = mysql_fetch_assoc($result);
		if ($row){
			$this->lastVisit = $row['lastVisit'];
			$this->totalVisits = $row['totalVisits'];
		}
	}
	
	private function logVisit(){
		if (($this->lastVisit + $this->timeOut) > time()){
			//log visit but don't bother incrementing counter
			$sql = "Update {$this->tableName} set lastVisit = '%s', totalVisits = '%s' where ipAddress='%s'";
			mysql_query(sprintf($sql, time(), $this->totalVisits, $this->ip));
		} else {
			if (empty($this->lastVisit)){
				//no records in the database
				$sql = "Insert into {$this->tableName} (ipAddress, lastVisit, totalVisits) values ('%s', '%s', '%s')";
				mysql_query(sprintf($sql, $this->ip, time(), 1));
			} else {
				//just update the database
				$sql = "Update {$this->tableName} set lastVisit = '%s', totalVisits = '%s' where ipAddress='%s'";
				mysql_query(sprintf($sql, time(), $this->totalVisits++, $this->ip));
			}
		}
	}
	
	private function populateIP(){
		//taken from [URL unfurl="true"]http://roshanbh.com.np/2007/12/getting-real-ip-address-in-php.html[/URL]
		if (!empty($_SERVER['HTTP_CLIENT_IP'])){
    		$ip=$_SERVER['HTTP_CLIENT_IP'];
		}elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])){
			$ip=$_SERVER['HTTP_X_FORWARDED_FOR'];
		} else {
			$ip=$_SERVER['REMOTE_ADDR'];
		}
  		$this->ip = $ip;
	}
}

?>

the table definition is something like this

create table whateverName
( 
  ipAddress varchar(15) PRIMARY KEY,
  lastVisit int(15),
  totalVisits int (10) 
)

 

[JoeAtRevolutionUnltd]

What would be a better way to go about something like this?

 

[jpadie]

that's a good question.

the best way is to use sessions to track users.

This works just fine except for:
* users that disable session cookies
* robots

but ... if a user visits lots of pages in a single visit then normally one would want to track these page impressions; as they will give valid information for your site statistics.

if you are trying to de-duplicate robot visits there may be other ways.

i'm happy to work on this with you, to come up with a generic logging class that can help others. Please explain in as much detail as you can what you are trying to achieve.

 

[JoeAtRevolutionUnltd]

I'm creating a music community web site where users will be able to add bands, shows, venues, photo galleries and news articles. On the home page I would like to be able to show the overall most popular items and also sort them by most popular band, show, venue, photo gallery and news article. Then on the landing page for each section I want to be able to have a featured show/band/venue etc. and the top 5 or something similar.

I came up with this code, which is very simple but seems to work well.

    $SQL = "SELECT * FROM news WHERE N_ID = $itemId";
	$result = MySQL_SubmitQuery($SQL,$cnnDB);

	while ($row = @adodb_fetch_assoc($result)) {
	    $views = $row['N_Views'] + 1;

	    if (!isset($_COOKIE[$itemId]))        //CHECK IF COOKIE WITH THIS ARTICLE ID STILL EXISTS / IF IT WAS VIEWED BY THE SAME PERSON LESS THAN 2 HRS AGO
            MySQL_SubmitQuery("UPDATE news SET N_Views = $views, N_Date_Viewed = NOW() WHERE N_ID = $itemId",$cnnDB);       //IF NO EXISTS COOKIE UPDATE VIEWS

        setcookie($itemId, $itemId, time()+7200);  // SET ARTICLE ID COOKIE - EXPIRES IN 2 HRS 
    	$row['N_Content'] = $row['N_Content']."<br><a href='/'><< Back</a>";
	    $returnVal[] = $row;
	}

	return $returnVal;