phpBeginner06 Posted January 22, 2007 Share Posted January 22, 2007 OK - so I posted a hit counter script on this forum and never could it to work; so now I am asking you guys how you would do this.How would I go about creating a unique hit counter based on cookies; that will post a unique visit to database:ie.Visits Page 26 http://www.domain.com 38 http://www.domain.com/page1 19 http://www.domain.com/page2I need to know like, what the MySQL table structure would look like and what the php code would be.I know this is asking alot, but right now I have a script that only record unique hits to a text file (cookie based). I want to record every unique hit to a database; that way I can display all my results in a web page using a MySQL Query (so I do not have to use "hard" html coding and php includes to display the number of page hits). Quote Link to comment Share on other sites More sharing options...
linuxdream Posted January 22, 2007 Share Posted January 22, 2007 I think a table structure just like you put with visits and page would work well. At the top of each page you wish to record as a hit, just include a quick query like:[code]<?php$page = $_SERVER['PHP_SELF'];//Assuming you are using a DB abstraction layer...$db$db->query('UPDATE hitsTable SET visits = visits + 1 where page = "$page" LIMIT 1');?>[/code] Quote Link to comment Share on other sites More sharing options...
phpBeginner06 Posted January 22, 2007 Author Share Posted January 22, 2007 linuxdream, This would work great for page views, but there is no way of knowing if this is a unique visitor with this script. I want to be able to set a cookie when a page is veiwed for the first time and send a "count + 1" to a specific field within a database datatable. If a person with my cookie (set from the prevoius visit) visites this web page again; the MySQL UPDATE count + 1 would not occur. Otherwise if my cookie is not set; MySQL would UPDATE the count of the datafield (ie count + 1). Say this field was called "Visits" and each time a new visitor (one without my cookie) views this page for the first time; the datatable field will count + 1 or count ++ (not sure if that is proper syntax or not - ie count + 1 or count ++). This will the datatable will look like this:Visits Page 4 http://www.domain.com 3 http://www.domain.com/page1 2 http://www.domain.com/page2and not like thisVisits Page 1 http://www.domain.com 1 http://www.domain.com 1 http://www.domain.com 1 http://www.domain.com 1 http://www.domain.com/page1 1 http://www.domain.com/page1 1 http://www.domain.com/page1 1 http://www.domain.com/page1 1 http://www.domain.com/page2 1 http://www.domain.com/page2I want a single data field to be updated by 1 visit for every non-cookied visit (ie new visitor).There must be a way to do this with a database MySQL_Query UPDATE and cookies. I know it is possible with a text file and cookies. Quote Link to comment Share on other sites More sharing options...
Ninjakreborn Posted January 22, 2007 Share Posted January 22, 2007 It can never be 100% accurate. just trap the ip, then you can use it like you wish. how many times different ip's hit one page, every page that ip visit's, seemingly how long they remain on that pageOr other things, the only thing you can really identify is the IP, and sometimes it can change. Quote Link to comment Share on other sites More sharing options...
phpBeginner06 Posted January 22, 2007 Author Share Posted January 22, 2007 businessman332211, The problem with an IP Address is that it's more likely to change before a cookie is to be erased. I have a script I found on another page, but I do not know what the code would be that I need in a function for: "get_new_user" or "record_hit".Here is the script below:[u]Data Table MySQL Code[/u][code]CREATE TABLE ur_hits ( page VARCHAR(30) NOT NULL, ref VARCHAR(40) NOT NULL, user_id INT UNSIGNED, counter INT UNSIGNED, PRIMARY KEY (page, user_id) );[/code][u]PHP Code[/u][code]<?$db=mysql_connect("localhost","username","password");mysql_select_db("Statistics",$db);if (!isset($user_id)) { $user_id=get_new_user(); setcookie ("user_id", $user_id, time()+315360000, "", "http://www.mydomain.com, 0); }record_hit($user_id, $PHP_SELF); mysql_query("UPDATE ur_hits SET counter=counter+1 WHERE page = '$PHP_SELF' AND user_id=\"$user_id\" ",$db); ?> <html><head> <title>Untitled</title></head><body></body></html>[/code][color=blue]Does anyone know what function I need to create for "get_new_user" and "record_hit" ???[/color] Quote Link to comment Share on other sites More sharing options...
linuxdream Posted January 22, 2007 Share Posted January 22, 2007 Well then simply test if the cookie is set before you increment the visit count:[code]<?phpif(!$_COOKIE['yourcookie']){ $page = $_SERVER['PHP_SELF']; //Assuming you are using a DB abstraction layer...$db $db->query('UPDATE hitsTable SET visits = visits + 1 where page = "$page" LIMIT 1');}?>[/code] Quote Link to comment Share on other sites More sharing options...
ShogunWarrior Posted January 22, 2007 Share Posted January 22, 2007 linuxdream's code is good because it will increase the visits on an existing row, instead of adding rows like you were worried about.Also, if after the DB Update query you check that [b]mysql_affect_rows[/b] is NOT greater than you can insert a new row for an unvisited URL. Quote Link to comment Share on other sites More sharing options...
phpBeginner06 Posted January 23, 2007 Author Share Posted January 23, 2007 linuxdream, I tried it like you said to (similar atleast) and the cookie works, but it still is not sending the variables to the database. I created a new table and used your script (similar atleast); below is the code.[u]MySQL Data Table[/u][code]CREATE TABLE `hitsTable` ( `visits` text NOT NULL, `page` text NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;[/code][u]PHP[/u][code]<?phpif (empty ($_COOKIE['visit'])){mysql_connect("localhost","username","password"); mysql_select_db("Statistics"); $page = $_SERVER['PHP_SELF'];mysql_query('UPDATE hitsTable SET visits = visits + 1 where page = "$page" LIMIT 1');setcookie ('visit', 1, time()+86400);}?>[/code]So what am I still doing wrong; why will it not post to database? Quote Link to comment Share on other sites More sharing options...
linuxdream Posted January 23, 2007 Share Posted January 23, 2007 So the $_SERVER['PHP_SELF'] has no value? Try a print_r($_SERVER) and see what the values are to be sure that everything is as it should be. If everything looks good then place your query into a variable and echo it out to be sure the query string looks good.[code]<?php//Use the following just to test your string/results$query = 'UPDATE hitsTable SET visits = visits + 1 where page = "$page" LIMIT 1';echo $query;if(!mysql_query($query)){ echo mysql_error();}else{ echo "Query was good!";}?>[/code]I don't see anything wrong off the top of my head. No error messages? In the logs?? Quote Link to comment Share on other sites More sharing options...
phpBeginner06 Posted January 23, 2007 Author Share Posted January 23, 2007 To everyone that helped me with this code; let me say [u][b][color=red]!!! THANK YOU !!![/color][/b][/u]. I finally got it to work the way that it is supposed to work and I am extremely happy about that.[u]MySQL Database & Data Table Script[/u][code]CREATE DATABASE `Statistics` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;USE Statistics;CREATE TABLE `hitsTable` ( `visits` text NOT NULL, `page` text NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;INSERT INTO `hitsTable` VALUES ('0', '/Sub-Directory-Name-Here/01-23-2007.php');[/code][u]PHP Hit Counter Script[/u][code]<?phpif (empty ($_COOKIE['visit'])){mysql_connect("localhost","username","password");mysql_select_db("Statistics");$page = $_SERVER['PHP_SELF'];mysql_query("UPDATE hitsTable SET visits=visits+1 WHERE page='$page'");setcookie ('visit', 1, time()+86400);}?>[/code] Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.