php列出html表中列和行中的mysql数据 [英] php listing mysql data in html table in columns and rows

问题描述

我的数据库，玩家和分数表中有2个表。我在这些表中有数据，并希望将得分表中的数据列出到HTML表。我希望玩家名称是列，玩家的分数是行。我已经看到了如何使用预定义列添加行的示例，但是没有看到如何添加将数据设置为列的示例。由于玩家名称在玩家表格中，我将不得不使用联接功能来显示玩家名称，不知道如何使用php进行此操作。我是php的新手，所以一直坚持这一点。

I have 2 tables in my database, player and score table. I have data in these table and want to list the data from my score table to a HTML Table. I want the player names to be the columns and the scores of the players to be the rows. I have seen examples of how to add rows with predefined columns, but haven't seen examples on how to add set the data as columns. Since player name is in player table I will have to use join function to display the player name, not sure how to do this with php. I am new to php, so have been stuck at this for a while.

数据库

Database

播放器表格：

playerid
name
lastname

得分表：

scoreid
score
playerid (foreign key with player.playerid)

我的分数数据包含ex：

My score data contains 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：
这就是我所拥有的现在工作，但它只显示名称。

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   |

..等等

尽可能请参阅播放器名称是列，分数是与播放器对应的行中的数据。

As you can see the player names are the columns and scores are the data in rows corresponding to the player.

想想它：

玩家表具有：

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) 

希望你现在能更好地理解

Hopefully you get a better understanding now

输出

Output

这是它之前的样子

现在结果如下：

推荐答案

让表格显示列中的行数据的最佳方法是使用表格中的表格，因为必须构建一个表格HTML中的表格逐行显示。

The best way to do get your table displaying "row-data in columns" is to use a table in a table, since you have to build a table in HTML row by row.

然后，要获得玩家的所有分数，您可以通过单独的请求为每个玩家请求数据，也可以使用GROUP_CONCAT。

Then, to get all scores of a player, you can either request the data for each player by a seperate request or use a GROUP_CONCAT.

$result = mysqli_query($conn,"SELECT p.*, (SELECT GROUP_CONCAT(s.score) FROM score s WHERE s.playerid = p.playerid) AS scorearray FROM player p");

现在你的结果将包含你的播放器表的所有列和一个scorearray列，其中包含一个以逗号分隔的播放器所有分数列表。 请参阅GROUP_CONCAT示例。

Now your result will contain all colums of your player table and a column "scorearray", which contains a comma-separated list of all scores from your player. See GROUP_CONCAT example.

要生成第一行，您可以重复使用给定的代码：

To generate the first row you can reuse your given code:

$result = mysqli_query($conn, "SELECT p.*, (SELECT GROUP_CONCAT(s.score) FROM score s WHERE s.playerid = p.playerid) AS scorearray FROM player p");


$playercols = array(); 
$scores = array(); 

while($row = mysqli_fetch_array($result)) 
{ 
    $playercols[] =  "<th>" . $row['name'] . "</th>"; 
    $currentscore = explode(",", $row['scorearray']); 
    // Only doing a line break, you can build a one-columned table out of this data aswell 
    $scores[] = "<td valign=top>" . implode("<br />", $currentscore) . "</td>"; 
} 
echo "<table>"; 
echo "<tr>"; 
echo implode("", $playercols); 
echo "</tr>"; 
echo "<tr>"; 
echo implode("", $scores); 
echo "</tr>"; 
echo "</table>"; 

在此处查看结果：结果

此处的完整来源：来源

这篇关于php列出html表中列和行中的mysql数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！