php 在列和行中列出 html 表中的 mysql 数据

Question

我的数据库中有 2 个表，player 和 score 表。我在这些表中有数据，想将我的分数表中的数据列出到 HTML 表中。我希望球员姓名成为列，球员的分数成为行。我已经看到了如何添加带有预定义列的行的示例，但还没有看到如何添加将数据设置为列的示例。由于播放器名称在播放器表中，我将不得不使用连接函数来显示播放器名称，但不确定如何使用 php 执行此操作。我是 php 的新手，所以已经坚持了一段时间。

Database

玩家表:

playerid
name
lastname

评分表:

scoreid
score
playerid (foreign key with player.playerid)

我的分数数据包含 ex:

scoreid: 1, score: 53, playerid: 1
scoreid: 2, score: 23, playerid: 1 
scoreid: 3, score: 12, playerid: 2 
scoreid: 4, score: 67, playerid: 3 
scoreid: 5, score: 31, playerid: 2  

PHP: This is what I have working for now but it only displays the name.

$result = mysqli_query($conn,"SELECT * FROM player");

echo "<table>
<tr>
<th>Name</th>
<th>Last Name</th>
</tr>";

while($row = mysqli_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['lastname'] . "</td>";
echo "</tr>";
}
echo "</table>";

Wanted Result

| Jonny  |  Mike  |  Bill  |
|   23   |   10   |   12   |
|   12   |   31   |   23   |

..等等

如您所见，球员姓名是列，分数是与球员对应的行中的数据。

可以这样想:

玩家表有:

id: 1. name: Jonny
id: 2. name: Mike
id: 3. name: Bill

分数表有:

scoreid: 1, score: 23, playerid: 1 (Jonny)
scoreid: 2, score: 12, playerid: 1 (Jonny)
scoreid: 3, score: 10, playerid: 2 (Mike)
scoreid: 4, score: 31, playerid: 2 (Mike)
scoreid: 5, score: 12, playerid: 3 (Bill)
scoreid: 6, score: 23, playerid: 3 (Bill) 

希望你现在能更好地理解

Output

这是之前的样子

现在的结果是这样的:

Answer 1

您必须按照以下行使用 JOIN:

$result = mysqli_query($conn,"SELECT player.*, score.* FROM player LEFT JOIN score ON score.playerid = player.playerid ");

echo "<table>
<tr>
<th>Name</th>
<th>Last Name</th>
<th>Score</th>
</tr>";

while($row = mysqli_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['lastname'] . "</td>";
echo "<td>" . $row['score'] . "</td>";
echo "</tr>";
}
echo "</table>";