Page 1 of 1
How to add visitor counter per ID page
Posted: Thu Aug 28, 2008 6:29 am
by admin2gd1
I want to add a page view counter and I want to be able to add this information to mysql database.
Here is my table structure:
Code: Select all
TABLE IF NOT EXISTS `properties` (
`property_id` int(11) NOT NULL auto_increment,
`visits` varchar(255) collate utf8_bin NOT NULL,
`last_visit` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`property_id`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
I would like it to count 1 per IP location so that it's fare and I also want to put a record of the last visit the page was viewed. So the page is viewed like this in the address bar: -
http://www.sitename.com/page.php?id=1
So the function has to work for each ID and not update the database for all ID's. So the moment the user goes to the page the information is sent to the database.
If someone can help me write this I would really appreciate it as I haven't experienced writing logging code.
Thanks.
Re: How to add visitor counter per ID page
Posted: Thu Aug 28, 2008 9:43 am
by dajawu
Are you assigning a Unique ID to each person, or do you just want to log based on IP address?
Re: How to add visitor counter per ID page
Posted: Fri Aug 29, 2008 12:40 pm
by admin2gd1
I would liked it logged per IP please.
Re: How to add visitor counter per ID page
Posted: Fri Aug 29, 2008 12:47 pm
by dajawu
Well ignoring the ID, you can do two things. One you can log all IPs even duplicates and use a query that does a GROUP BY ip but then you will have a lot of records or you can run a query that checks for an existing IP, then just do an UPDATE WHERE IP = $ip.
Re: How to add visitor counter per ID page
Posted: Fri Aug 29, 2008 12:58 pm
by DaiWelsh
Firstly I would dispense with the whole IP concept and do this with cookies; record a hit on the page and then set a cookie. That way the same user (strictly same machine) will not count as another visitor unless the cookie expires or they delete it.
basic code (untested) for one property only
Code: Select all
if(!isset($_COOKIES['visited'])) {
mysql_query('UPDATE properties SET visits = visits + 1 WHERE property_id = '.$id);
}
however to do it for multiple properties you will want to store all properties visited into one cookie e.g. as a serialised array and then check the specific index for the current property.
Alternatively you could have a seperate property_visits table, assign a unique ID into a cookie for the user and then store which properties they have visited in the DB. I have not gone for this approach above as it does not scale well for all scenarios and I don't know how many visitors/properties you will have. The DB approach does however offer much more potential for data mining later if that is important to you (e.g. average number of properties a visitor visits).
The problem(s) with using IP are 1) it forces you to have the extra DB table 2) IP is _not_ a reliable gauge of individual web users, some proxy IP addresses cover thousands (if not more) of users.
If I had more time I would do a full solution but I don't right now, sorry.
HTH,
Dai
Re: How to add visitor counter per ID page
Posted: Mon Sep 01, 2008 7:53 am
by admin2gd1
This is the lastest updated code but it still doesn't work, I'm sure it is much better than before but something is still missing.
Code: Select all
<?php
///// Collect information about the property /////
$sql = "SELECT * FROM ".TBL_PROPERTIES." WHERE property_id='$id' AND sale_type='1' AND status='1' AND active='1'";
$query = mysql_query($sql);
while ($result = @mysql_fetch_array($query)){
$id = $result['$property_id'];
$sale_type = $result['sale_type'];
$visits = $result['visits'];
$lastvisit = $result['$last_visit'];
}
///// Checks if property has been sold or removed. /////
if ($sale_type == 0){
header("Location: delisted.php?id=$id");
}
///// If property has not been sold or removed, continue to display property details. /////
///// Update Property Views Counter /////
if($visits = mysql_query("SELECT COUNT (visits) FROM ".TBL_PROPERTIES." WHERE property_id='$id' AND visits='$visits'")){
while($row = mysql_fetch_assoc($visits)){
$counter = $visits; //count the number of times the property has been viewed
if ($counter != "" || !empty($counter)){
$visits_counter = $counter +1; //add 1 to the counter when property is viewed
$lastvisit = date("Y:m:d H:i:s"); // update the date when property was last looked at
$sqlvisits = mysql_query("UPDATE ".TBL_PROPERTIES." SET visits='$visits_counter', last_visit='$lastvisit' WHERE property_id='$id'");
$query = mysql_query($sqlvisits);
}
}
}
?>