NogDog;11059985 wrote:more efficient
That was my first thought; no matter what you're trying to do, you're going to want efficiency above all, and accuracy second. Keep in mind, about accuracy, that several things affect this: programming bugs are only one item ... what if someone navigates away from a page while it's loading and the server doesn't finish the request? What about bots? Anyway ...
First, about using a text file. The way you're doing it seems kind of expensive. Read a file, increment a counter in PHP, write a file. There may be little help for this. You might want to only do one kind of counting for efficiency's sake.
Second, about the DB. Read from the DB, count different stuff in PHP (morley, tushino, etc.), write back to DB. Also sounding expensive.
What do you really want to do? You want to record a hit (of a certain type), and display, approximately, the number of hits on the page. Then, for your own personal use, you want fairly accurate reports.
Although you think it's simple, I'm not sure that it is in this case. It's not rocket science, though (whew! if it was, we wouldn't have stats here at the office 🙂 )
I'm not an expert on using text files for this, as it's generally considered inefficient, I've not done anything like that for quite a while. If you have to use one, I might suggest just doing a write of "1" to the file with FILE_APPEND set when you want to record a hit. If you want to read,
$hitcount = strlen(file_get_contents($file));
That doesn't really save much (it's still a write and a read), but it could be helpful. For example, if you want the page to display "Number of page views", that's just a read. And you might run a cron(8) job and update it every minute or 5 minutes, depending on your traffic (less reads?)
//a cronjob
<?php
$hitcount = strlen(file_get_contents($my_counter_file));
file_put_contents("/www/mysite.com/includes/hitcount.html", "Number of Views: $hitcount");
?>
And then include "hitcount.html" in your pages.
Now, I've done quite a bit of "tracker" work with MySQL. I might qualify as "decent", at least, in that.
In all lingos of SQL, "select * from table" is generally not as efficient as "select something_specific from table". [Preferably something small---I didn't realize for quite a while that a good deal of MySQL's time is spent actually sending data to the caller ... so the less data you have to send, the faster it will run ;-)] You might look at your DB design. Maybe something like this:
mysql> create table hitcount (id int auto_increment primary key, ts int, type int);
mysql >describe hitcount;
+----+------------+------+
| id | ts | type |
+----+------------+------+
| 1 | 1486483232 | 1 |
| 2 | 1486483237 | 2 |
| 3 | 1486483241 | 1 |
| 4 | 1486483243 | 4 |
+----+------------+------+
So instead of a column for each of morley, tushino, etc., just use an integer key. 1=morley, 2=tushino etc.
Now, if you want "morley hits":
$sql = "select id from hitcount where type = 1;";
$res = $conn->query($db, $sql);
$morley_hits = $res->num_rows;
And since "id" is the indexed primary key, this will run quite fast. You're already using $result->num_rows ... this is superior to COUNT() in MySQL, although COUNT(ID) has gotten better with the last several iterations.
Your insert, of course, something like:
switch ($hit_type) {
case "morley":
$type = 1;
break;
//etc
}
$sql = "insert into hitcount (ts, type) values (UNIX_TIMESTAMP(), $type);";
$res = $conn->query($sql);
HTH,
