-
Notifications
You must be signed in to change notification settings - Fork 4
/
calculate_ip_statistics.php
202 lines (185 loc) · 6.45 KB
/
calculate_ip_statistics.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
<!-- /**
* @author Keeleycenc
* @param none
* @version 1.2
* @link https://keeleycenc.com
*/ -->
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>LightweightTracker</title>
<style>
h1 {
color: #e74c3c;
border-bottom: 2px solid #e74c3c;
padding-bottom: 10px;
margin-bottom: 20px;
font-size: 14px;
}
h2 {
color: #3498db;
border-bottom: 2px solid #3498db;
padding-bottom: 10px;
margin-bottom: 20px;
font-size: 14px;
}
code {
display: block;
white-space: pre-wrap;
background-color: #373b3d;
border: 1px solid #2c3032;
padding: 20px;
overflow-x: auto;
line-height: 1.6;
font-size: 14px;
color: #d4d4d4;
margin-bottom: 20px;
border-radius: 8px;
box-shadow: 0 4px 8px rgba(0, 0, 0, 0.2);
}
code span {
display: block;
padding: 5px 0;
}
.success {
color: #5cb85c;
font-weight: bold;
}
.error {
color: #d9534f;
font-weight: bold;
}
.pagination {
text-align: center;
margin-top: 20px;
}
.pagination a, .pagination span {
display: inline-block;
padding: 5px 10px;
margin-right: 5px;
border: 1px solid #ddd;
color: #337ab7;
text-decoration: none;
}
.pagination span.current-page {
font-weight: bold;
color: #fff;
background-color: #337ab7;
border-color: #337ab7;
}
</style>
</head>
<body>
<code>
<?php
// 数据库连接信息
$servername = "localhost";
$username = "用户名";
$password = "密码";
$dbname = "数据库名";
// 创建数据库连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检查连接是否成功
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
// 创建新表保存计算结果,仅在第一次运行时创建
$newTableName = "ip_access_summary";
$sqlCreateTable = "CREATE TABLE IF NOT EXISTS $newTableName (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
IP VARCHAR(50) NOT NULL,
TotalCount INT(6) NOT NULL,
LastAccessTime DATETIME NOT NULL,
FirstAccessTime DATETIME NOT NULL,
Address VARCHAR(255) NOT NULL,
UNIQUE KEY (IP) -- 确保IP字段为唯一键,避免重复插入
)";
if ($conn->query($sqlCreateTable) === TRUE) {
echo "
<h1>/**
* @author Keeleycenc
* @Time 2023/07/11
* @brief LightweightTracker轻量追踪器
* @param none
* @retval uniqueIPCount;
ip;totalCount;
lastAccessTime;
firstAccessTime;
address;
* @note 简单而高效尽管它没有高级功能
* @version 1.2
* @link https://keeleycenc.com
*/</h2>\n
<h2>* This lightweight tracker is designed for simplicity and efficiency. It provides basic tracking
* functionality for monitoring user visits, including IP address analysis and access statistics.
* While it may not have advanced features, its minimalistic approach makes it easy to integrate
* and suitable for projects where a simple visit tracking solution is preferred.</h2>";
} else {
echo "<span class='error'>插入数据失败: " . $conn->error . "</span>";
}
// 分页设置
$currentPage = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$recordsPerPage = 25;
$offset = ($currentPage - 1) * $recordsPerPage;
// /计算总页数
$sqlPageCount = "SELECT COUNT(DISTINCT ip) AS count FROM visitors";
$totalCountResult = $conn->query($sqlPageCount);
$totalCountRow = $totalCountResult->fetch_assoc();
$totalPages = ceil($totalCountRow['count'] / $recordsPerPage);
//获取带有当前页面偏移量和限制的结果
$sqlFetchResults = "SELECT
ip AS IP,
COUNT(ip) AS TotalCount,
MAX(time) AS LastAccessTime,
MIN(time) AS FirstAccessTime,
address AS Address
FROM visitors
GROUP BY ip
ORDER BY LastAccessTime DESC
LIMIT $offset, $recordsPerPage";
$results = $conn->query($sqlFetchResults);
// 统计不同IP地址的总数量
$uniqueIPCount = 0;
// 将计算结果插入新表中,使用 INSERT INTO ... ON DUPLICATE KEY UPDATE 来确保数据插入或更新
if ($results->num_rows > 0) {
while ($row = $results->fetch_assoc()) {
$ip = $row["IP"];
$totalCount = $row["TotalCount"];
$lastAccessTime = $row["LastAccessTime"];
$firstAccessTime = $row["FirstAccessTime"];
$address = $row["Address"];
$sqlInsert = "INSERT INTO $newTableName (IP, TotalCount, LastAccessTime, FirstAccessTime, Address)
VALUES ('$ip', $totalCount, '$lastAccessTime', '$firstAccessTime', '$address')
ON DUPLICATE KEY UPDATE TotalCount = VALUES(TotalCount), LastAccessTime = VALUES(LastAccessTime), FirstAccessTime = VALUES(FirstAccessTime), Address = VALUES(Address)";
if ($conn->query($sqlInsert) !== TRUE) {
echo "<span class='error'>插入数据失败: " . $conn->error . "</span>";
} else {
echo "<span class='success'>Data replacement successful: IP=$ip, TotalCount=$totalCount, LastAccessTime=$lastAccessTime, FirstAccessTime=$firstAccessTime, Address=$address</span>";
}
// 增加不同IP地址的总数量
$uniqueIPCount++;
}} else {
echo "<span class='error'>没有数据需要处理。</span>";
}
// 如果有多个页面,则输出分页链接
if ($totalPages > 1) {
echo "<div class='pagination'>";
for ($i = 1; $i <= $totalPages; $i++) {
if ($currentPage == $i) {
echo "<span class='current-page'>$i</span>";
} else {
echo "<a href='?page=$i'>$i</a>";
}
}
echo "</div>";
}
// 输出不同IP地址的总数量
echo "<span class='success'>Valid visitor: $uniqueIPCount</span>";
// 关闭数据库连接
$conn->close();
?>
</code>
</body>
</html>