報表頁需顯示所有站點流量,包含「零流量站點」。直接 LEFT JOIN + GROUP BY 寫法易出錯且效能差。改用兩次查詢 + PHP 陣列合併更清晰高效。
-- 站點設定表 (讀多寫少 → MyISAM)
CREATE TABLE site_config (
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
sid SMALLINT UNSIGNED NOT NULL UNIQUE COMMENT '站點ID',
site_url VARCHAR(128) NOT NULL,
site_name VARCHAR(80) NOT NULL,
add_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- 流量統計表 (寫多讀少 → InnoDB)
CREATE TABLE site_stat (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
sid SMALLINT UNSIGNED NOT NULL,
ip_address VARCHAR(32) NOT NULL,
add_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- SQL1: 所有站點
SELECT sid, site_url, site_name FROM site_config;
-- SQL2: 今日各站點流量數
SELECT sid, COUNT(1) AS come_total
FROM site_stat
WHERE add_time >= '2024-01-15 00:00:00'
AND add_time <= '2024-01-15 23:59:59'
GROUP BY sid;
<?php
/**
* 查詢並以指定欄位為鍵回傳二維陣列
* @param string $sql SQL 語句
* @param string|null $keyField 指定作為鍵的欄位名,null 則回傳數字索引陣列
* @return array
*/
function getList($sql, $keyField = null) {
$res = mysqli_query($GLOBALS['link'], $sql); // 假設全域連線
$data = [];
if ($keyField === null) {
while ($row = mysqli_fetch_assoc($res)) {
$data[] = $row;
}
} else {
while ($row = mysqli_fetch_assoc($res)) {
$key = $row[$keyField] ?? null;
if ($key !== null) {
$data[$key] = $row;
}
}
}
mysqli_free_result($res);
return $data;
}
// 使用
$sites = getList($sql1); // [0=>['sid'=>200,...], 1=>['sid'=>201,...], ...]
$stats = getList($sql2, 'sid'); // [200=>['sid'=>200,'come_total'=>3], 202=>...]
// 報表輸出
foreach ($sites as $site) {
$sid = $site['sid'];
$count = $stats[$sid]['come_total'] ?? 0;
echo "{$site['site_url']} (ID:{$sid}) : {$count} PV<br>";
}
/*
輸出:
baidu.com (ID:200) : 3
google.com (ID:201) : 0
cnblogs.com (ID:202) : 3
codejia.net (ID:203) : 1
*/
?>
function getListPdo(PDO $pdo, $sql, $keyField = null) {
$stmt = $pdo->query($sql);
if ($keyField === null) return $stmt->fetchAll(PDO::FETCH_ASSOC);
$data = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$key = $row[$keyField] ?? null;
if ($key !== null) $data[$key] = $row;
}
return $data;
}