I have used a JOIN to select the data from a variety of tables as shown below.

image 1 - query output

I am looking to output this to a HTML table using PHP in the way it's formatted below (1 of these tables needs to be created for each different game_id):

image 2 - desired HTML table

I'm a bit lost because in all the SQL/PHP/HTML table examples I've looked up, I haven't found an example where it isn't just converting the output of an SQL query directly into a HTML table without manipulating it. e.g. https://www.w3schools.com/Php/php_mysql_select.asp.

Is anyone able to point me in the right direction as to the best way to achieve this? I'm not sure if I should try to write a better SQL query which better matches what I need or try to use PHP to do the heavy lifting.

Note: any team could have 1 or many players.

Thanks

CodePudding user response：

I think that the problem does not have a technical solution in Database, but I have the solution. What do you think about the first statement after it, and then the second statement and after it, and so on

An example of what I say

1. players: Oliver,Mathew,Jackson
2. scors : 100,850,400
3. goals : 2,7,5
4. falls : 3,4,6

If this does not satisfy the explanation, explain why

CodePudding user response：

Well, as far as the SQL query goes, you're already done.

Molding the eventual html output to look like you desire, is from there on all up to the application (meaning PHP).

The first step is to crudely just recreate what you see on image 1 - query output using PHP & HTML. I imagine you've already managed (or can manage) to get to that point ?

The code for that would look something like:

$records = $db->query('SELECT .....');
echo "<table>";
foreach ($records as $record) {
    printf('<tr>
            <td>%s</td>
            <td>%s</td>
            <td>%s</td>
            </tr>', 
        htmlentities($record['map_name']), 
        htmlentities($record['first_name']), 
        htmlentities($record['score'])
    );
}
echo "</table>";

Once you have that working, you can mold it to your liking. First you want to group up the records per game, and within that also per round/team.

$records = $db->query('SELECT .....'); // same as before
$games = []; // new buffer array
foreach ($records as $record) {
    $thisGameID = (int)    $record['game_id'];
    $thisTeam   = (string) $record['team'];
    // If thisGameID didnt already exist in $games, we add it (as an empty array)
    if (!isset($games[ $thisGameID ])) $games[ $thisGameID ] = [];
    // If thisTeam doesnt already exist within there, we add that aswell (as an empty array)
    if (!isset($games[ $thisGameID ][ $thisTeam ])) $games[ $thisGameID ][ $thisTeam ] = [];
    // Now we can add this record to that
    $games[ $thisGameID ][ $thisTeam ][] = $record;
}

Now you have a seperate recordset grouped per match per team. to create the output do something like

    foreach ($games as $thisGameID => $game) { // Iterate the buffer, per match
        foreach ($game as $teamIndex => $records) { // And iterate within that, per team
            foreach ($records as $rowIndex => $record) {
                $teamHeader = ''; // Empty placeholder for a header field (the '<td rowspan=X>...</td>' field)
                if ($rowIndex === 0) { // If this is the first row for this match/team-combo
                    $roundRowCount = count($records); // How many rows are there for this match/team-combo
                    $teamWinColumn = $teamIndex . '_round_wins';
                    $teamHeader = sprintf(
                        '<td rowspan="%d">%s round wins %s</td>',
                        $roundRowCount,
                        htmlentities($record['team']),
                        isset($record[ $teamWinColumn ]) // We have to account for the wins-column perhaps not existing
                            ? (int) $record[ $teamWinColumn ]
                            : 'Nothing i guess!'
                    );
                }
                printf('<tr>
                        %s
                        <td>%s</td>
                        <td>%s</td>
                        <td>%s</td>
                        </tr>',
                    $teamHeader,
                    htmlentities($record['map_name']), 
                    htmlentities($record['first_name']), 
                    htmlentities($record['score'])
                );
            }
        }
    }