关于下拉菜单、数据库、关联选项的问题
更新了一下版本，代码要重新弄，请教大家！
需要达成的效果是：
1、数据库里有match_results表，有字段：game、year、match、mname、point字段；
2、game下拉菜单是从数据库里game字段中调取，选择相应的game后，会决定year的选项（也是从数据库里调取）,选择year后，会影响match的选项，以此类推。
我现在这个代码，没有办法形成这种关联~麻烦大家帮我看下~~如何修改可好。
另外，我这个页面会放在主页的一个
<?php
// 数据库连接配置
$host = '127.0.0.1';
$dbname = 'games';
$user = 'root';
$password = '1111';
try {
// 创建PDO实例
$pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $user, $password, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
]);
} catch (PDOException $e) {
die("数据库连接失败: " . $e->getMessage());
}
// 查询游戏列表
try {
$stmt = $pdo->prepare('SELECT DISTINCT game FROM match_results ORDER BY game DESC');
$stmt->execute();
$gameList = $stmt->fetchAll(PDO::FETCH_COLUMN);
} catch (PDOException $e) {
die("查询游戏列表失败: " . $e->getMessage());
}
// 查询其他列表
$gamelist = queryList('game', 'match_results');
$yearList = queryList('year', 'match_results');
$matchList = queryList('match', 'match_results');
$mnameList = queryList('mname', 'match_results');
// 辅助函数来查询不同的列表
function queryList($column, $table) {
global $pdo;
$list = [];
try {
$stmt = $pdo->prepare("SELECT DISTINCT `$column` FROM `$table` ORDER BY `$column` DESC");
$stmt->execute();
$list = $stmt->fetchAll(PDO::FETCH_COLUMN);
} catch (PDOException $e) {
die("查询$table的$column失败: " . $e->getMessage());
}
return $list;
}
// 设置默认选中的值
$selectedGame = isset($_GET['game']) ? $_GET['game'] : '';
$selectedYear = isset($_GET['year']) ? $_GET['year'] : '';
$selectedMatch = isset($_GET['match']) ? $_GET['match'] : '';
$selectedMname = isset($_GET['mname']) ? $_GET['mname'] : '';
// 渲染HTML表单
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<link href="css/bootstrap-4.4.1.css" rel="stylesheet" type="text/css">
<title>Matches Selector</title>
<div class="container"><h1>团体积分 | Points List Of Delegations</h1></div>
<div class="container" style="border-radius: 15px; background-color: #595959";>
<form id="form" action="" method="get">
<!-- 第1个下拉框 -->
<select id="game-select" name="game" onchange="this.form.submit();">
<option value="">请选择</option>
<?php foreach ($gamelist as $game): ?>
<option value="<?php echo htmlspecialchars($game); ?>" <?php echo $game === $selectedGame ? 'selected' : ''; ?>><?php echo htmlspecialchars($game); ?></option>
<?php endforeach; ?>
</select>
<!-- 第2个下拉框 -->
<select id="year-select" name="year" onchange="this.form.submit();">
<option value="">请选择</option>
<?php foreach ($yearList as $year): ?>
<option value="<?php echo htmlspecialchars($year); ?>" <?php echo $year === $selectedYear ? 'selected' : ''; ?>><?php echo htmlspecialchars($year); ?></option>
<?php endforeach; ?>
</select>
<!-- 第3个下拉框 -->
<select id="match-select" name="match" onchange="this.form.submit();">
<option value="">请选择</option>
<?php foreach ($matchList as $match): ?>
<option value="<?php echo htmlspecialchars($match); ?>" <?php echo $match === $selectedMatch ? 'selected' : ''; ?>><?php echo htmlspecialchars($match); ?></option>
<?php endforeach; ?>
</select>
<!-- 第4个下拉框 -->
<select id="mname-select" name="mname" onchange="this.form.submit();">
<option value="">请选择</option>
<?php foreach ($mnameList as $mname): ?>
<option value="<?php echo htmlspecialchars($mname); ?>" <?php echo $mname === $selectedMname ? 'selected' : ''; ?>><?php echo htmlspecialchars($mname); ?></option>
<?php endforeach; ?>
</select>
</form>
</div>
<!--选定之后，显示比赛信息区域 -->
<p></p>
<?php
// 确保输入是安全的
$game = $_GET['game'] ?? '';
$year = $_GET['year'] ?? '';
$match = $_GET['match'] ?? '';
$mname = $_GET['mname'] ?? '';
// 使用预处理语句进行查询
$stmt = $pdo->prepare("SELECT delegation, SUM(point) AS points FROM match_results WHERE game = ? AND `year` = ? AND `match` = ? AND `mname` = ? GROUP BY delegation ORDER BY points DESC");
$stmt->execute([$game, $year, $match, $mname]);
$delegations = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<div name="div_main" class="container" style="border-radius: 20px; background-color:#F8F8F8;">
<table class="table" style="font-family: inherit;">
<tr align="center" style="font-weight:bold;">
<td>序号</td>
<td>代表队/Delegation</td>
<td>积分/Points</td>
<td>备注/Remark</td>
</tr>
<?php
$serial_number = 1;
foreach ($delegations as $row):
?>
<tr align="center">
<td><?php echo $serial_number; ?></td>
<td><?php echo htmlspecialchars($row['delegation']); ?></td>
<td><?php echo htmlspecialchars($row['points']); ?></td>
<td></td>
</tr>
<?php
$serial_number++;
endforeach;
?>
</table>
</div>
</body>
</html>
