Getting a unique number of hits in php

Question

I want to add a unique hit counter to my site using PHP. This counter will store the visitor's IP address for each page in the database. I have a database structure:

The hits table with two columns:

 ip page_url 

My question is: after getting the visitor's IP address in a PHP file, which is better (for performance)?

• To check if the IP address is already in the database. And when not already in the database, add it
• Just add all the IP addresses of visitors (without re-checking), and then get separate IP addresses for the corresponding page to get a unique hit counter.

Answer 1

If you are in MySQL, you can abuse the combination of PRIMARY KEY and ON DUPLICATE KEY UPDATE:

 CREATE TABLE hits ( ip VARCHAR(15), page_url VARCHAR(200), PRIMARY KEY (ip,page_url), hitcount INT NOT NULL DEFAULT 0 ) 

Now on the page you get

 INSERT INTO hits(ip, page_url,hitcount) VALUES('$ip','$url',1) ON DUPLICATE KEY UPDATE hitcount=hitcount+1 

Why is this?

• Another unique key is POISON for a table with a record, so avoid this. Actually.
• INSERT ... ON DUPLICATE KEY UPDATE only blocks the row once

You can also record the timestamp of the last access:

 ALTER TABLE hits ADD COLUMN lastseen TIMESTAMP(); 

Answer 2

Assuming you are comfortable with simple programming.

NOT EXCLUSIVELY "REAL TIME", BUT ALMOST REAL TIME I highly recommend that you log in your own format to a text file (if you do not like the Apache [customlog][1] function).

Then create a cronjob every 5 minutes or even once every 1 minute, if you want to close the "live" one, import the text into the MySql temporary table into a large gulp with LOAD DATA INFILE , and then update your visit table based on GROUP BY ip.

TOTALLY REAL TIME This can be a huge drag to your server, but assuming you have light traffic, just create two tables in MySQL. One only writes the read article / page identifier + IP + time (log table). The other contains the article / page identifier and the number of visits - where the counts are updated by GROUP BY ip in the first table.

Answer 3

using Eugen Rieck I made my code with a repository of time and date.

This code stores the user ip, account, date and time.

 $ipaddress = ''; if (getenv('HTTP_CLIENT_IP')) $ipaddress = getenv('HTTP_CLIENT_IP'); else if(getenv('HTTP_X_FORWARDED_FOR')) $ipaddress = getenv('HTTP_X_FORWARDED_FOR'); else if(getenv('HTTP_X_FORWARDED')) $ipaddress = getenv('HTTP_X_FORWARDED'); else if(getenv('HTTP_FORWARDED_FOR')) $ipaddress = getenv('HTTP_FORWARDED_FOR'); else if(getenv('HTTP_FORWARDED')) $ipaddress = getenv('HTTP_FORWARDED'); else if(getenv('REMOTE_ADDR')) $ipaddress = getenv('REMOTE_ADDR'); else $ipaddress = 'UNKNOWN'; // final ip address $time=date("Y/m/d H:i:s"); // date and time in a single variable $sql = "INSERT IGNORE INTO `ipaddress` (`id`, `ipaddress`, `count`, `time`) VALUES ('', '$ipaddress','1', '$time') ON DUPLICATE KEY UPDATE count=count+1, time='".$time."'"; // adding ip,count, date and time to table if($conn->query($sql) === false) { trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR); } else { $last_inserted_id = $conn->insert_id; $affected_rows = $conn->affected_rows; } $conn->close(); 

Answer 4

I would use a unique key ( ip , page_url ), optionally date . If the record does not exist, it will create, it will lead to an error otherwise (but you can easily check the error code), or you can use IGNORE in INSERT .

There will be only one query to the database and will probably be the fastest. You would also automatically guarantee that all records would be unique and that you would not need to use a transaction.

Answer 5

Hi John and his colleagues ... I am new to programming and MYSQL ... however, the way I circumvented it was as follows:

I use some PHP code to query the DB and then evaluate both columns, ip and page_url , and if they evaluate to true, do nothing, otherwise ... paste / execute the code shown above by Eugene Rick ...

 $ip = $_SERVER['REMOTE_ADDR']; // Get IP Address. $purl = htmlspecialchars($_GET['page_url']); // Get Page URL. $results = $mysqli->query("select * from hits"); // Query hits table. $row = $results->fetch_assoc(); // Fetch array and assign to $row, then evaluate with if statement. if ($row['ip'] == $ip && $row['page_url'] == $purl) { } else { $mysqli->query("insert into hits (ip,page_url,counter) values ('$ip','$purl',1) on duplicate key update counter=counter+1"); } 

To be honest, I did not check this on more than one IP address, just mine.

However, it adds rows to the hit table for each URL inserted based on a unique ip (the one I am currently using), and does not increase the number of hits for any inserted URL when the page refreshes, so I assume that she works...