Mysql/html how to get output like this, or similar

Question

PhpMyAdmin

Looking for output like this:

In Mysql/PHP, how can I get output like this, or similar (photo 2)?

$start = mysqli_query($conn, $sql);

if (mysqli_num_rows($start) > 0) {
    $row = mysqli_fetch_assoc($start);

    while($row = mysqli_fetch_assoc($start)) {
        echo '<p name='.$row['game'].'>'.$row['game'].'</p>' ;
    }
}

Answer 1

The key is that the result set must be ordered by the game you want to span across multiple columns.

Then, you need to figure out how many rows should each game cell span for.

Then you have to keep track of which game is the latest one found and either omit the table cell if you've already seen the game, or add a <td colspan="..."> if it's a game mentioned for the first time.

Take a look at the example code

$data = [
    ['id' => 1, 'nickname' => 'abc', 'game' => 'abc', 'checkbox' => 1],
    ['id' => 2, 'nickname' => 'xyz', 'game' => 'zyx', 'checkbox' => 0],
    ['id' => 5, 'nickname' => 'xyz', 'game' => 'abc', 'checkbox' => 1],
    ['id' => 6, 'nickname' => 'afg', 'game' => 'zyx', 'checkbox' => 1],
];

// The data must be sorted by the game, that's the key
$sortedData = [
    ['id' => 1, 'nickname' => 'abc', 'game' => 'abc', 'checkbox' => 1],
    ['id' => 5, 'nickname' => 'xyz', 'game' => 'abc', 'checkbox' => 1],
    ['id' => 2, 'nickname' => 'xyz', 'game' => 'zyx', 'checkbox' => 0],
    ['id' => 6, 'nickname' => 'afg', 'game' => 'zyx', 'checkbox' => 1],
];


// Figure out which game should span how many rows ahead of time
foreach ($sortedData as $row) {
    $gameCounts[$row['game']] ??= 0;
    $gameCounts[$row['game']]++;    
}

echo '<table border="1">';
echo '<thead>';
echo '<tr>';
foreach (array_keys($sortedData[0]) as $header) {
    echo '<th>'.$header.'</th>';
}
echo '</tr>';
echo '</thead>';

echo '<tbody>';
$previousGame = null;
foreach ($sortedData as $row) {
    echo '<tr>';
    foreach ($row as $key => $value) {
        if ($key === 'game') { // The Game column gets a special treatment
            if ($previousGame !== $value) { // The game has changed - span the cell over the correct number of rows
                echo '<td rowspan="'.$gameCounts[$value].'">'.$value.'</td>';
                $previousGame = $value;
            }
            
            continue;
        }
        
        echo '<td>'.$value.'</td>';
    }
    echo '</tr>';
}
echo '</tbody>';
echo '</table>';

it will produce the HTML code you need, assuming your code holds the sorted result of the SQL query in the $sortedData array. See it live,https://3v4l.org/LITPO#v8.3.9