本文目录导读：
我来帮你设计一个PHP项目统计足球比赛中红黄牌数量的方案,这里提供两种常见的场景解决方案：
基础统计（适合小型项目）
数据库设计
-- 球队表
CREATE TABLE teams (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
yellow_cards INT DEFAULT 0,
red_cards INT DEFAULT 0
);
-- 罚牌记录表
CREATE TABLE cards (
id INT PRIMARY KEY AUTO_INCREMENT,
team_id INT,
player_name VARCHAR(50),
card_type ENUM('yellow', 'red'),
match_date DATE,
FOREIGN KEY (team_id) REFERENCES teams(id)
);
PHP统计代码
<?php
class CardCounter {
private $pdo;
public function __construct($pdo) {
$this->pdo = $pdo;
}
// 统计所有球队的罚牌情况
public function getTeamCardStats() {
$sql = "SELECT
t.name,
t.id,
SUM(CASE WHEN c.card_type = 'yellow' THEN 1 ELSE 0 END) AS yellow_count,
SUM(CASE WHEN c.card_type = 'red' THEN 1 ELSE 0 END) AS red_count
FROM teams t
LEFT JOIN cards c ON t.id = c.team_id
GROUP BY t.id, t.name
ORDER BY yellow_count DESC, red_count DESC";
return $this->pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
}
// 找出罚牌最多的球队
public function getMostPunishedTeam() {
$teams = $this->getTeamCardStats();
if (empty($teams)) {
return null;
}
// 计算总分（红牌权重大于黄牌）
foreach ($teams as &$team) {
$team['score'] = $team['yellow_count'] + ($team['red_count'] * 2) + $team['red_count'];
}
usort($teams, function($a, $b) {
return $b['score'] - $a['score'];
});
return $teams[0];
}
// 添加罚牌记录
public function addCard($teamId, $playerName, $cardType) {
$sql = "INSERT INTO cards (team_id, player_name, card_type, match_date)
VALUES (?, ?, ?, NOW())";
$stmt = $this->pdo->prepare($sql);
return $stmt->execute([$teamId, $playerName, $cardType]);
}
}
// 使用示例
$pdo = new PDO('mysql:host=localhost;dbname=football', 'username', 'password');
$cardCounter = new CardCounter($pdo);
// 获取所有球队统计
$stats = $cardCounter->getTeamCardStats();
foreach ($stats as $team) {
echo "{$team['name']}: 黄牌{$team['yellow_count']}张, 红牌{$team['red_count']}张<br>";
}
// 获取罚牌最多的球队
$mostPunished = $cardCounter->getMostPunishedTeam();
if ($mostPunished) {
echo "罚牌最多的球队是: {$mostPunished['name']}";
}
?>
更复杂的统计（适合多场比赛）
使用汇总视图
<?php
// 多场比赛的统计
class TournamentStats {
public function getTournamentCardRanking($tournamentId) {
$sql = "SELECT
t.name,
COUNT(CASE WHEN c.card_type = 'yellow' THEN 1 END) AS total_yellows,
COUNT(CASE WHEN c.card_type = 'red' THEN 1 END) AS total_reds,
COUNT(CASE WHEN c.card_type = 'yellow' THEN 1 END) +
(COUNT(CASE WHEN c.card_type = 'red' THEN 1 END) * 3) AS weighted_score
FROM teams t
JOIN matches m ON t.id IN (m.home_team_id, m.away_team_id)
JOIN cards c ON m.id = c.match_id AND t.id = c.team_id
WHERE m.tournament_id = ?
GROUP BY t.id, t.name
ORDER BY weighted_score DESC";
$stmt = $this->pdo->prepare($sql);
$stmt->execute([$tournamentId]);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
// 对比两支球队
public function compareTwoTeams($teamAId, $teamBId) {
$teamStats = [];
foreach ([$teamAId, $teamBId] as $teamId) {
$sql = "SELECT
t.name,
SUM(CASE WHEN c.card_type = 'yellow' THEN 1 ELSE 0 END) AS yellow,
SUM(CASE WHEN c.card_type = 'red' THEN 1 ELSE 0 END) AS red
FROM teams t
LEFT JOIN cards c ON t.id = c.team_id
WHERE t.id = ?
GROUP BY t.id";
$stmt = $this->pdo->prepare($sql);
$stmt->execute([$teamId]);
$teamStats[] = $stmt->fetch(PDO::FETCH_ASSOC);
}
// 比较函数
$comparison = [];
$comparison[] = [
'team' => $teamStats[0]['name'],
'total_cards' => $teamStats[0]['yellow'] + $teamStats[0]['red'],
'severity' => $teamStats[0]['yellow'] + ($teamStats[0]['red'] * 2)
];
$comparison[] = [
'team' => $teamStats[1]['name'],
'total_cards' => $teamStats[1]['yellow'] + $teamStats[1]['red'],
'severity' => $teamStats[1]['yellow'] + ($teamStats[1]['red'] * 2)
];
return $comparison;
}
}
// 展示对比结果
$comparison = $tournamentStats->compareTwoTeams($teamA, $teamB);
if ($comparison[0]['severity'] > $comparison[1]['severity']) {
echo "{$comparison[0]['team']} 罚牌更多/更严重";
} elseif ($comparison[0]['severity'] < $comparison[1]['severity']) {
echo "{$comparison[1]['team']} 罚牌更多/更严重";
} else {
echo "两队罚牌情况相同";
}
?>
前端展示 + 交互
<!-- HTML模板 -->
<!DOCTYPE html>
<html>
<head>球队罚牌统计</title>
<style>
table { border-collapse: collapse; width: 100%; }
th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
th { background-color: #f2f2f2; }
.card-img { width: 20px; height: 30px; }
</style>
</head>
<body>
<h1>球队红黄牌统计</h1>
<table>
<thead>
<tr>
<th>排名</th>
<th>球队</th>
<th>黄牌</th>
<th>红牌</th>
<th>总罚牌</th>
<th>危险指数</th>
</tr>
</thead>
<tbody>
<?php foreach ($stats as $index => $team): ?>
<tr>
<td><?php echo $index + 1; ?></td>
<td><?php echo htmlspecialchars($team['name']); ?></td>
<td>
<?php echo $team['yellow_count']; ?>
<span style="background:yellow; display:inline-block; width:12px; height:15px;"></span>
</td>
<td>
<?php echo $team['red_count']; ?>
<span style="background:red; display:inline-block; width:12px; height:15px;"></span>
</td>
<td><?php echo $team['yellow_count'] + $team['red_count']; ?></td>
<td>
<?php
$dangerScore = $team['yellow_count'] + ($team['red_count'] * 2);
echo $dangerScore;
// 显示危险等级
if ($dangerScore >= 10) {
echo " 🔴极高";
} elseif ($dangerScore >= 7) {
echo " 🟠高";
} elseif ($dangerScore >= 4) {
echo " 🟡中等";
} else {
echo " 🟢低";
}
?>
</td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</body>
</html>
关键点说明
- 统计逻辑：红牌通常权重高于黄牌（1红牌 ≈ 2-3黄牌）
- 可扩展性：可以按赛季、比赛类型等维度筛选
- 性能优化：大数据量时使用索引和缓存
推荐：根据项目复杂度选择合适的方案，初学者建议从方案一开始,逐步扩展功能。
