Php Listing Mysql Data In Html Table In Columns And Rows

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

Solution 1:

You have to use a JOIN, along the lines of:

$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>";

Solution 2:

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.

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");

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>"; 

See the result here: Result

And full source here: Source