使用PHP+MySql实现站点统计功能

笔记 · 07-06 · 178 人浏览
使用PHP+MySql实现站点统计功能

Q:

使用PHP实现站点的UV统计、今日PV、总PV统计的功能并支持在前端输出相关数据

A:

以下是一个使用 PHP 实现站点的 UV（独立访客）统计、今日 PV（页面浏览量）、总 PV 统计的功能，并在前端输出相关数据的示例代码：
(这是最终的成品，期间在豆包AI的辅助下：添加了一个前端输出API、自动化新建相关的数据表、实现单独统计页面的功能)。最终使用js在前端输出统计的数据

<?php
// 连接数据库
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

$conn = new mysqli($servername, $username, $password, $dbname);

// 检查连接
if ($conn->connect_error) {
    die("连接失败: ". $conn->connect_error);
}

// 获取页面参数
$page = isset($_GET['page'])? $_GET['page'] : 'default';

// 如果表不存在，则创建表
$sql_create_table = "CREATE TABLE IF NOT EXISTS page_views (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    page VARCHAR(50) NOT NULL,
    ip VARCHAR(50) NOT NULL,
    date DATE NOT NULL
)";

if ($conn->query($sql_create_table) === FALSE) {
    echo "创建表时出错: ". $conn->error;
}

// 获取客户端 IP 地址
$ip = $_SERVER['REMOTE_ADDR'];

// 获取当前日期
$today = date('Y-m-d');

// 插入数据
$sql_insert = "INSERT INTO page_views (page, ip, date) VALUES ('$page', '$ip', '$today')";

if ($conn->query($sql_insert) === FALSE) {
    echo "插入数据时出错: ". $conn->error;
}

// 计算今日 PV （特定页面）
$sql_today_pv = "SELECT COUNT(*) AS today_pv FROM page_views WHERE page = '$page' AND date = '$today'";
$result_today_pv = $conn->query($sql_today_pv);
$row_today_pv = $result_today_pv->fetch_assoc();
$today_pv = $row_today_pv['today_pv'];

// 计算总 PV （特定页面）
$sql_total_pv = "SELECT COUNT(*) AS total_pv FROM page_views WHERE page = '$page'";
$result_total_pv = $conn->query($sql_total_pv);
$row_total_pv = $result_total_pv->fetch_assoc();
$total_pv = $row_total_pv['total_pv'];

// 计算 UV （特定页面，假设通过用户 IP 来区分）
$sql_uv = "SELECT COUNT(DISTINCT ip) AS uv FROM page_views WHERE page = '$page'";
$result_uv = $conn->query($sql_uv);
$row_uv = $result_uv->fetch_assoc();
$uv = $row_uv['uv'];

// 关闭数据库连接
$conn->close();

header('Content-Type: application/json');
echo json_encode([
    'today_pv' => $today_pv,
    'total_pv' => $total_pv,
    'uv' => $uv
]);
?>

通过JS在前端输出统计的数据：

<!DOCTYPE html>
<html>

<body>

  <h2>站点统计</h2>
  <div id="today-pv"></div>
  <div id="total-pv"></div>
  <div id="uv"></div>

  <script>
    fetch('stats.php')
     .then(response => response.json())
     .then(data => {
        document.getElementById('today-pv').innerHTML = '今日 PV：' + data.today_pv;
        document.getElementById('total-pv').innerHTML = '总 PV：' + data.total_pv;
        document.getElementById('uv').innerHTML = 'UV：' + data.uv;
      })
     .catch(error => console.error('获取数据时出错：', error));
  </script>

</body>

</html>

在实际使用中请确保将 'stats.php' 替换为您实际的 PHP 文件的路径。同时，也要确保 PHP 文件能够正确返回所需的统计数据的 JSON 格式。
当然你只需要在显示统计数据的区块加入对应的id并使用js获取对应的数据即可。

拓展内容（未测试）

添加点赞功能并优化相关的交互逻辑，最后做了一丢丢美化
示例代码

<?php
// 连接数据库
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

$conn = new mysqli($servername, $username, $password, $dbname);

// 检查连接
if ($conn->connect_error) {
    die("连接失败: ". $conn->connect_error);
}

// 获取页面参数
$page = isset($_GET['page'])? $_GET['page'] : 'default';

// 如果表不存在，则创建表
$sql_create_table = "CREATE TABLE IF NOT EXISTS page_views (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    page VARCHAR(50) NOT NULL,
    ip VARCHAR(50) NOT NULL,
    date DATE NOT NULL,
    likes INT(11) DEFAULT 0
)";

if ($conn->query($sql_create_table) === FALSE) {
    echo "创建表时出错: ". $conn->error;
}

// 获取客户端 IP 地址
$ip = $_SERVER['REMOTE_ADDR'];

// 获取当前日期
$today = date('Y-m-d');

// 插入或更新数据（包括点赞数初始化为 0）
$sql_insert = "INSERT INTO page_views (page, ip, date, likes) VALUES ('$page', '$ip', '$today', 0) ON DUPLICATE KEY UPDATE likes = likes";

if ($conn->query($sql_insert) === FALSE) {
    echo "插入或更新数据时出错: ". $conn->error;
}

// 处理点赞操作
if (isset($_POST['like'])) {
    $sql_increment_likes = "UPDATE page_views SET likes = likes + 1 WHERE page = '$page'";
    if ($conn->query($sql_increment_likes) === FALSE) {
        echo "点赞操作时出错: ". $conn->error;
    }
}

// 计算今日 PV （特定页面）
$sql_today_pv = "SELECT COUNT(*) AS today_pv FROM page_views WHERE page = '$page' AND date = '$today'";
$result_today_pv = $conn->query($sql_today_pv);
$row_today_pv = $result_today_pv->fetch_assoc();
$today_pv = $row_today_pv['today_pv'];

// 计算总 PV （特定页面）
$sql_total_pv = "SELECT COUNT(*) AS total_pv FROM page_views WHERE page = '$page'";
$result_total_pv = $conn->query($sql_total_pv);
$row_total_pv = $result_total_pv->fetch_assoc();
$total_pv = $row_total_pv['total_pv'];

// 计算 UV （特定页面，假设通过用户 IP 来区分）
$sql_uv = "SELECT COUNT(DISTINCT ip) AS uv FROM page_views WHERE page = '$page'";
$result_uv = $conn->query($sql_uv);
$row_uv = $result_uv->fetch_assoc();
$uv = $row_uv['uv'];

// 计算点赞数 （特定页面）
$sql_likes = "SELECT SUM(likes) AS total_likes FROM page_views WHERE page = '$page'";
$result_likes = $conn->query($sql_likes);
$row_likes = $result_likes->fetch_assoc();
$total_likes = $row_likes['total_likes'];

// 关闭数据库连接
$conn->close();

header('Content-Type: application/json');
echo json_encode([
    'today_pv' => $today_pv,
    'total_pv' => $total_pv,
    'uv' => $uv,
    'total_likes' => $total_likes
]);
?>

前端代码

<!DOCTYPE html>
<html>

<head>
  <style>
  .like-btn {
      width: 50px;
      height: 50px;
      background-image: url('https://example.com/claw.png'); /* 替换为您的击爪图片的链接 */
      background-size: cover;
      border: none;
      cursor: pointer;
    }

  .like-btn.clicked {
      background-image: url('https://example.com/another_claw.png'); /* 替换为被点击后的图片链接 */
    }
  </style>
</head>

<body>

  <h2>站点统计</h2>
  <div id="today-pv"></div>
  <div id="total-pv"></div>
  <div id="uv"></div>
  <div id="total-likes"></div>
  <button class="like-btn" onclick="likePage(this)"> </button>

  <script>
    function likePage(button) {
      button.classList.add('clicked');
      fetch('stats.php', {
          method: 'POST',
          body: new URLSearchParams({
            like: 1
          })
        })
    .then(response => response.json())
    .then(data => {
            document.getElementById('today-pv').innerHTML = '今日 PV：' + data.today_pv;
            document.getElementById('total-pv').innerHTML = '总 PV：' + data.total_pv;
            document.getElementById('uv').innerHTML = 'UV：' + data.uv;
            document.getElementById('total-likes').innerHTML = '总点赞数：' + data.total_likes;
          })
    .catch(error => console.error('获取数据或点赞时出错：', error));
    }

    // 初始获取数据
    // 假设要统计的页面是 'page1'
    var page = 'page1';
    fetch('stats.php?page=' + page)
  .then(response => response.json())
  .then(data => {
        document.getElementById('today-pv').innerHTML = '今日 PV：' + data.today_pv;
        document.getElementById('total-pv').innerHTML = '总 PV：' + data.total_pv;
        document.getElementById('uv').innerHTML = 'UV：' + data.uv;
        document.getElementById('total-likes').innerHTML = '总点赞数：' + data.total_likes;
      })
  .catch(error => console.error('获取数据时出错：', error));
  </script>

</body>

</html>
网站相关
  1. 花坊有约 (作者)  07-06
    回复

    不太会写代码，在网络上搜索了半天，代码基本上实现的都不太完美或是看不懂，最后在豆包AI的加持下实现了该功能。
    可以在首页底部查看到本站首页的uv数据。

©2023 - 2024|花坊有约-让每一次相遇都有意义.
THEME JASMINE · FOREVERBLOG