在 PHP/MySQL 中计算页面浏览量的最佳方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4762527/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the best way to count page views in PHP/MySQL?
提问by Joe
And by best I mean most efficient, right now placing this on my post.php file is the only thing I can think of:
最好我的意思是最有效，现在将它放在我的 post.php 文件中是我唯一能想到的：
$query = mysql_query(" UPDATE posts SET views + 1 WHERE id = '$id' ");
is there a better way, a method that would consume less server resources. I ask because if this was a small app I would have no problem with the above, but I am trying to build something that will be used by a lot of people and I want to be as query conscious as possible.
有没有更好的方法，一种消耗更少服务器资源的方法。我问是因为如果这是一个小应用程序，我不会有上述问题，但我正在尝试构建一些会被很多人使用的东西，我想尽可能地注意查询。
回答by Kyle Wild
If you're interested in conserving resources and still using SQL for reporting, and precise # doesn't matter, you could try sampling like this (modify sample rate to suit your scale):
如果您对节约资源感兴趣并且仍然使用 SQL 进行报告，并且精确的 # 并不重要，您可以尝试这样采样（修改采样率以适合您的规模）：
$sample_rate = 100;
if(mt_rand(1,$sample_rate) == 1) {
$query = mysql_query(" UPDATE posts SET views = views + {$sample_rate} WHERE id = '{$id}' ");
// execute query, etc
}
回答by Kyle Wild
If memcache is an option in your server environment, here's another cool way to sample, but also keep up with the precise number (unlike my other answer):
如果 memcache 是您的服务器环境中的一个选项，这是另一种很酷的采样方式，但也要跟上精确的数字（与我的其他答案不同）：
function recordPostPageView($page_id) {
$memcache = new Memcached(); // you could also pull this instance from somewhere else, if you want a bit more efficiency*
$key = "Counter for Post {$page_id}";
if(!$memcache->get($key)) {
$memcache->set($key, 0);
}
$new_count = $memcache->increment($key);
// you could uncomment the following if you still want to notify mysql of the value occasionally
/*
$notify_mysql_interval = 100;
if($new_count % $notify_mysql_interval == 0) {
$query = mysql_query("UPDATE posts SET views = {$new_count} WHERE id = '{$page_id}' ");
// execute query, etc
}
*/
return $new_count;
}
- And don't mind purists crying foul about Singletons. Or you could pass it into this function, if you're more purist than pragmatist :)
- 不要介意纯粹主义者对单身人士大喊大叫。或者你可以把它传递给这个函数，如果你比实用主义者更纯粹:)
回答by Kamil Tom?ík
IMHO best solution is to have views_count stored inside memory (memcached, whatever), and do updates in memory. (Of course updates have to be synchronized)
恕我直言，最好的解决方案是将 views_count 存储在内存中（memcached，无论如何），并在内存中进行更新。（当然更新要同步）
Then you can use cron script which will push those values to db. (after some time - seconds, minutes, whatever.)
然后您可以使用 cron 脚本将这些值推送到 db。（一段时间后 - 秒，分钟，等等。）
回答by Click4Knowledge
You can also check these lines of code. I think it will be helpful because you can achieve your goal with just a text file. It does not require any database activity.
您还可以检查这些代码行。我认为这会很有帮助，因为您只需一个文本文件即可实现目标。它不需要任何数据库活动。
<?php
session_start();
$counter_name = "counter.txt";
// Check if a text file exists. If not create one and initialize it to zero.
if (!file_exists($counter_name)) {
$f = fopen($counter_name, "w");
fwrite($f,"0");
fclose($f);
}
// Read the current value of our counter file
$f = fopen($counter_name,"r");
$counterVal = fread($f, filesize($counter_name));
fclose($f);
// Has visitor been counted in this session?
// If not, increase counter value by one
if(!isset($_SESSION['hasVisited'])){
$_SESSION['hasVisited']="yes";
$counterVal++;
$f = fopen($counter_name, "w");
fwrite($f, $counterVal);
fclose($f);
}
echo "You are visitor number $counterVal to this site";回答by pradip kor
in the database there is only one column ipwith primary key defined and then store ip in database using PHP code below:
在数据库中只有一列ip定义了主键，然后使用下面的 PHP 代码将 ip 存储在数据库中：
Connection file :
连接文件：
<?php
$conn = mysqli_connect("localhost","root","");
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$db=mysqli_select_db($conn,"DB_NAME");
if(!$db)
{
echo "Connection failed";
}
?>
PHP file:
PHP文件：
<?php
$ip=$_SERVER['REMOTE_ADDR'];
$insert="INSERT INTO `id928751_photography`.`ip` (`ip`)VALUES ('$ip');";
$result = mysqli_query($conn,$insert);
?>
show count :
显示计数：
<?php
$select="SELECT COUNT(ip) as count from ip;";
$run= mysqli_query($conn,$select);
$res=mysqli_fetch_array($run);
echo $res['count'];
?>
using this method in the database store all server ip
使用此方法在数据库中存储所有服务器ip
NOTE: only server ip can store or count not device ip
注意：只有服务器 ip 可以存储或计数而不是设备 ip
回答by Mano Kovacs
You could keep a counter-array in cache (like APC or Memcache) and increase the counter for certain posts in that. Then store the updates once a while. You might loose some views if a cache-reset occures
您可以在缓存中保留一个计数器数组（如 APC 或 Memcache）并增加其中某些帖子的计数器。然后存储更新一次。如果发生缓存重置，您可能会丢失一些视图
Other solution would be to keep a separate table for visits only (Field: postid, visits). That is the fasters you can get from mysql. Try to use InnoDB engine, since it provides row-level-locking!
其他解决方案是为访问保留一个单独的表（字段：postid，visits）。这是您可以从 mysql 获得的更快的速度。尝试使用 InnoDB 引擎，因为它提供了行级锁定！
回答by Penny Liu
This way show how many actual people viewed your website not just how many times they viewed your website.
这种方式显示有多少实际用户浏览了您的网站，而不仅仅是他们浏览了您网站的次数。
Step1: Connecting to MySQL
步骤 1：连接到 MySQL
dbconfig.php
数据库配置文件
try
{
// Returns DB instance or create initial connection
$pdo = new PDO("mysql:host={$DB_host};port={$DB_port};dbname={$DB_name};charset=utf8mb4",$DB_user,$DB_pass);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e)
{
echo $e->getMessage();
}
Step2: Creating MySQL table
步骤 2：创建 MySQL 表
--
-- Table structure for table `unique_visitors`
--
CREATE TABLE `unique_visitors` (
`date` date NOT NULL,
`ip` text COLLATE utf8_unicode_ci NOT NULL,
`views` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Step3: Create a visitor counter by using IP address.
Step3：使用IP地址创建访客计数器。
<?php
require_once("dbconfig.php");
// Returns current date in YYYY-MM-DD format
$date = date("Y-m-d");
// Stores remote user ip address
$userIP = $_SERVER['REMOTE_ADDR'];
// Query for selecting record of current date from the table
$stmt = $pdo->prepare("SELECT * FROM unique_visitors WHERE date=:date");
$stmt->execute(['date' => $date]);
if(count($stmt->fetchAll()) === 0){
// Block will execute when there is no record of current date in the database table
$data = [
'date' => $date,
'ip' => $userIP,
];
// SQL query for inserting new record into the database table with current date and user IP address
$sql = "INSERT INTO unique_visitors (date, ip) VALUES (:date, :ip)";
$pdo->prepare($sql)->execute($data);
}else{
$row = $stmt->fetchAll(PDO::FETCH_ASSOC);
// Will execute when current IP is not in database
if(!preg_match('/'.$userIP.'/i',$row['ip'])){
// Combines previous and current user IP address with a separator for updating in the database
$newIP = "$row[ip] $userIP";
$data = [
'ip' => $newIP,
'date' => $date,
];
$sql = "UPDATE unique_visitors SET ip=:ip, views=views+1 WHERE date=:date";
$pdo->prepare($sql)->execute($data);
}
}
?>
