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

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?

CodePudding user response：

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

CodePudding user response：

Not the most elegant but you could group each set first and and loop through the grouped data for the output. However it would probably more efficient using SQL to group the data this way.

<?php

// Original Data
$originalData = [
    [
        'ID' => 1,
        'ID_KEY' => 1,
        'VALUE' => "Text1.1"
    ],
    [
        'ID' => 2,
        'ID_KEY' => 1,
        'VALUE' => "Text1.2"
    ],
    [
        'ID' => 3,
        'ID_KEY' => 1,
        'VALUE' => "Text1.3"
    ],
    [
        'ID' => 4,
        'ID_KEY' => 2,
        'VALUE' => "Text2.1"
    ],
    [
        'ID' => 5,
        'ID_KEY' => 2,
        'VALUE' => "Text2.2"
    ],
    [
        'ID' => 6,
        'ID_KEY' => 2,
        'VALUE' => "Text2.3"
    ],
    [
        'ID' => 7,
        'ID_KEY' => 3,
        'VALUE' => "Text3.1"
    ],
    [
        'ID' => 8,
        'ID_KEY' => 3,
        'VALUE' => "Text3.2"
    ],
        [
        'ID' => 9,
        'ID_KEY' => 3,
        'VALUE' => "Text3.3"
    ],
];

// Group the Data by ID_KEY
$groupedData = [];

foreach($originalData as $item) {
    $groupedData[$item['ID_KEY']][] = $item['VALUE'];
}

// HTML Output  
$output = "<table>";
    $output .= "<tr>";
        $output .= "<th>ID_KEY</th>";
        $output .= "<th>VALUE1</th>";
        $output .= "<th>VALUE2</th>";
        $output .= "<th>VALUE3</th>";
    $output .= "</tr>";

    // Output each row
    foreach($groupedData as $idKey => $groupData) {
         $output .= "<tr>";
            $output .= "<td>" . $idKey . "</td>";
            
            // Iterrate of each value
            foreach($groupData as $value) {
                $output .= "<td>" . $value . "</td>";
            }
        $output .= "</tr>";
    }
$output .= "</table>";


// Output the HTML
'<pre>' . print_r($output, true) . '</pre>';