I am running two small websites, this one and then the other one on digital design. For both of them I would like to see basic access statistics. I was using for a long time "navrcholu.cz" for this, but this is not available anymore. I was also experimenting with Google Analytics, but, frankly, I am not a fan of cookies.
So I was pondering what solution to choose... I wanted something, which is
Finally, I really like to learn new things and this adventure would bring me into the server-side scripting world. I had the chance to use all what I know about SQL and learn PHP ... - sharpen the tools I do not use in my daily life of a guy dealing with microelectronics.
And... in the end I decided to write this brief description of the solution since someone can find it useful or inspiring.
To be able to debug the whole thing locally I installed XAMPP. HTML and PHP code I am writing in Visual Studio Code.PHPAdmin is used on the webhosting side to manage the database.
The access counter solution is composed of the following components:
There is a database behind the counter, created in MySQL, stored on the server side. You need to set it up and choose some login name and password for it. The following SQL code was used to create the database, database schema is apparent from it (and very simple, here):
CREATE DATABASE web_counter_database; USE web_counter_database; CREATE TABLE web_counter_table ( url varchar(512), counter int );
If you are in doubt what are the commands doing, consult SQL Statement reference for MySQL.
The table web_counter_table contains the following columns:
The content of the table can look like the following for some hypothetical site
url (varchar) | counter (int) |
---|---|
page_1 | 15 |
/my_project/page2.php | 24 |
page_2 | 63 |
The PHP script handling the counter is very simple, the important part of it is excerpted below. Place the code into a dedicated file like counter.php. The code was simplified before I publsihed it here, but all necessary is there.
Note:
<html> <body> <?php $dbc = mysqli_connect('localhost', 'username_for_the_db', 'password_for_the_db', 'web_counter_database'); if (!$dbc) { echo ("Something got wrong!"); echo ("Message: ". mysqli_connect_error()."<br>"); } $escaped_uri = mysqli_real_escape_string($dbc, $_SERVER['PHP_SELF']); $query = "START TRANSACTION"; $result = mysqli_query($dbc, $query); $query = "SELECT counter FROM web_counter_table WHERE url=\"".$escaped_uri."\""; $result = mysqli_query($dbc, $query); $counter_last = 0; $num_rows = mysqli_num_rows($result); if ((!$result)||($num_rows==0)) { $counter_last = 0; $query = "INSERT INTO web_counter_table (url, counter) VALUES (\"".$escaped_uri."\", 1)"; } else { $row = mysqli_fetch_row($result); $counter_last = $row[0]; $query = "UPDATE web_counter_table SET counter = counter + 1 WHERE url=\"".$escaped_uri."\""; } echo $counter_last+1; $result = mysqli_query($dbc, $query); if (!$result) { echo "Something went wrong<br>"; } $query = "COMMIT"; $result = mysqli_query($dbc, $query); ?> </body> </html>
The PHP code is then to be invoked from any page where you would like to count the accessed, example follows below. I intentionaly include it in each page so as I can change the whole system in one place - counter.php.
<footer> You are visitor no. <?php include 'counter.php'; ?> </footer>
I wrote a simple PHP code reporting the counter statistics in a table. You can place it e.g., in counter_report.php, simplified code follows below. Note that anyone can open this page in the browser, there is no security. As it is, counter values can be publicly accessed.
<html> <body> <h1>Counter statistics</h1> <table style="border: 1px solid black"> <tr> <th>Page</th> <th>Counter</th> </tr> <?php $total = 0; $dbc = mysqli_connect('localhost', 'username_for_the_db', 'password_for_the_db', 'web_counter_database'); if (!$dbc) { echo ("Something got wrong!"); echo ("Message: ". mysqli_connect_error()."<br>"); } $query = "SELECT * FROM web_counter_table"; $result = mysqli_query($dbc, $query); $num_rows = mysqli_num_rows($result); if ((!$result)||($num_rows==0)) { #report that all is empty... } else { while ($row = mysqli_fetch_row($result)) { echo("<tr><td>".$row[0]."</td><td>".$row[1]."</td></tr>"); $total = $total + $row[1]; } } echo("<tr><td><b>Total</b></td><td><b>".$total."</b></td></tr>"); ?> </table> </body>
The report than looks like the following after you open the page in the browser (something like www.yourserver.com/counter_report.php):
Counter statisticsPage | Counter |
---|---|
page_1 | 15 |
/my_project/page2.php | 24 |
page_2 | 63 |
Total | 102 |
The solution has some limitations and some potential improvements are also obvious and possible. Selected items from my "todo list" for the future development follows:
And, by the way, you are visitor no.
1716 since the last moment I reset the counter.The whole package with the PHP source codes and list of SQL command to create the database schema is available here.
In addition to what is described above the package contains also user trace monitoring: reporting on the sequences of web pages visited by the "hashed" visitors. Using it I can see how the user went through the web and optimize the pages accordingly. Again, the monitoring works only with hashes, no user data are stored, there. See the companion page to see more.
By using information at this site, you agree that the author is not liable to you or to any other party for any direct, indirect, incidental, special, consequential or exemplary damages, including but not limited to, damages for loss of profits, goodwill, use, data, or other intangible losses, regardless of whether the author was advised of the possibility of such damage. In no event shall author's liability exceed zero U.S. dollar (USD 0.00) in aggregate. In the case that applicable law may not allow the limitation or exclusion of liability or incidental or consequential damages, the above limitation or exclusion may not apply to you, although author's liability will be limited to the fullest extent permitted by applicable law.