How can I print this data in a HTML table with this format?

Imagine that you have this SQL query result:

ID     ID_KEY     VALUE

1      1          Text1.1
2      1          Text1.2
3      1          Text1.3
4      2          Text2.1
5      2          Text2.2
6      2          Text2.3
7      3          Text3.1
8      3          Text3.2
9      3          Text3.3

And you want to print a table taking into account the ID_KEY, like this:

ID_KEY     VALUE1     VALUE2     VALUE3

1          Text1.1    Text1.2    Text1.3
2          Text2.1    Text2.2    Text2.3
3          Text3.1    Text3.2    Text2.3

How can I do that? I want to print a new row when ID_KEY changes. For example, now I have this code:

    $result = $con->query($sql);
    
    if ($result->num_rows > 0) {
        $res = "<table>";
            $res .= "<tr>";
                $res .= "<th>ID_KEY</th>";
                $res .= "<th>VALUE1</th>";
                $res .= "<th>VALUE2</th>";
                $res .= "<th>VALUE3</th>";
            $res .= "</tr>";
            while ($row=mysqli_fetch_assoc($result)) {
                $res .= "<tr>";
                    $res .= "<td>" . $row['ID_KEY'] . "</td>";
                    $res .= "<td>" . $row['VALUE1'] . "</td>";
                    $res .= "<td>" . $row['VALUE2'] . "</td>";
                    $res .= "<td>" . $row['VALUE3'] . "</td>";
                $res .= "</tr>";
            }
        $res .= "</table>";

        return $res;
    }

This code won’t work because “value1”, “value2” and “value3”, are fields that do not exist in my table.

If instead I say this:

$res .= "<tr>";
    $res .= "<td>" . $row['ID_KEY'] . "</td>";
    $res .= "<td>" . $row['VALUE'] . "</td>";
    $res .= "<td>" . $row['VALUE'] . "</td>";
    $res .= "<td>" . $row['VALUE'] . "</td>";
$res .= "</tr>";

This won’t work either, as the value of “VALUE” will be repeated 3 times.

Is it possible to do this or should I restructure the database to store the information in another way?

Answer

The code below would do the trick, but it is dependent on the content of your database and the query. Make sure there are always 3 values per ID_KEY and that the results are sorted properly. The idea is that you repeat to output cells with a value, until the ID_KEY changes. Many minor variations are possible, but in the end they all work on this principle.

$result = $con->query($sql);

if ($result->num_rows > 0) {
    $res = "<table>";
        $res .= "<tr>";
            $res .= "<th>ID_KEY</th>";
            $res .= "<th>VALUE1</th>";
            $res .= "<th>VALUE2</th>";
            $res .= "<th>VALUE3</th>";
        $res .= "</tr>";
        $row = mysqli_fetch_assoc($result);
        while ($row) {
            $idKey = $row['ID_KEY'];
            $res .= "<tr>";
            $res .= "<td>" . $idKey . "</td>";
            $res .= "<td>" . $row['VALUE'] . "</td>";
            while (($row = mysqli_fetch_assoc($result)) &&
                   ($idKey == $row['ID_KEY'])) {
                $res .= "<td>" . $row['VALUE'] . "</td>";
            }
            $res .= "</tr>";
        }
    $res .= "</table>";

    return $res;
}