php - 尝试从一张表中获取所有数据

Question

我试图从一个表中获取所有数据并使用另一个表中的 id 输出它。我的团队有成员，每个成员都有一组分数。

我需要使用我的第三个表来输出成员和分数，该表保存成员和团队的 ID，即 team_members 表。

每个成员有 8 个分数，这些分数是单个值或 1-5 之间的整数，不多也不少。我想简单地输出旁边有分数的行中的成员。我真的很挣扎:

我将来自表单输入的团队 ID 存储在 session 中，以便我的选择查询可以查看该 ID 以向我显示来自该特定团队的成员:

$sql = "SELECT t.team_name AS team_name, m.score_1, GROUP_CONCAT(m.firstName, ' ', m.lastName) AS team_members 
FROM members AS m
JOIN team_members AS tm
ON tm.member_id = m.member_id 
JOIN teams AS t
ON t.team_id = tm.team_id
WHERE t.dashboard_id = $dashboard_id AND t.team_id = $teamSelect
GROUP BY t.team_name"; 

if(!$result = $conn->query($sql)) {}

while($row = $result->fetch_assoc()){

echo '<h2>' . $row["team_name"] . '</h2><br>';

$names = explode(',', $row['team_members']);

echo '<div class="teamTable">';

echo '<div class="tableHeader">';
echo '<div class="col">&nbsp;</div>';
echo '<div class="col">SDO</div>';
echo '<div class="col">DCTO</div>';
echo '<div class="col">ED</div>';
echo '<div class="col">CA</div>';
echo '<div class="col">DHPT</div>';
echo '<div class="col">IRT</div>';
echo '<div class="col">GL</div>';
echo '<div class="col">IL</div>';
echo '</div>';
} 

我现在要做的是按团队ID按行输出成员姓名

foreach($names as $name) {
$namearray = preg_split('/\s+/', $name);
echo '<div class="tableNameRow">';
echo '<div class="col">' . $name . '</div>';
$sql2 = "SELECT score_1 FROM members WHERE firstName + lastName = '" . $name . "'";
$result2 = $conn->query($sql2);
if($result2->num_rows > 0){
while($row2 = $result2->fetch_assoc()){
echo '<div class="col">' . $row2['score_1'] . '</div>';
}
echo '<br>';
}
echo '</div>';
}

我想做的就是按行显示每个成员，他们的 8 个分数在他们旁边，如下所示:

member1 1 2 3 4 5 2 3 4
member2 4 3 2 5 1 2 4 2
member3 2 3 4 5 5 4 3 1

我只在我的 SELECT 中包含了 Score_1 来捕获这个痛点。上面的代码中发生的情况是，它按行输出每个用户，但随后从每个成员中获取 Score_1 并将其放在与成员姓名相同的行上。

表格

members
member_id(PK), firstName, lastName, score_1, score_2, score_3, score_4, score_5, score_6, score_7, score_8 

teams
team_id(PK), team_name

team_members
team_member_id, member_id(FK), team_id(FK)

Answer 1

对于任何想知道我通过更改选择来解决的人:

$dansql2 = "SELECT team_members.team_id, team_members.member_id, members.member_id, members.firstName, members.lastName, members.score_1, members.score_2, members.score_3, members.score_4, members.score_5, members.score_6, members.score_7, members.score_8
            FROM team_members 
            JOIN members
            ON team_members.member_id = members.member_id
            WHERE members.dashboard_id = $dashboard_id AND team_members.team_id = $teamSelect
            ORDER BY members.firstName ASC";