Include column names in query results or query comparison

Question

I am using the below to compare two select results and to display the differences between them.
It works as expected but I am unable to also include the corresponding column names.

Can someone tell me how I have to amend my code to get the desired result as in the example below?

PHP:

$stmt = $conn->prepare("SELECT * FROM locations WHERE vstId = ?");
$stmt->bind_param("s", $vstId);
$stmt->execute();
$resultBefore = $stmt->get_result()->fetch_row();
$stmt->store_result();

$stmt->close();

// some event

$stmt = $conn->prepare("SELECT * FROM locations WHERE vstId = ?");
$stmt->bind_param("s", $vstId);
$stmt->execute();
$resultAfter = $stmt->get_result()->fetch_row();
$stmt->store_result();

$stmt->close();
$conn->close(); 

for($i=0; $i<count($resultAfter); $i++) {
    if($resultAfter[$i] != $resultBefore[$i]) {
        $diff.= '<tr><td>' . $resultBefore[$i] . '</td><td>' . $resultAfter[$i] . '</td></tr>';
    }
}
echo '<table>' . $diff . '</table>';

Current result:

<table>
    <tr>
        <td>10408</td><td>10407</td>
    </tr>
    <tr>
        <td>168</td><td>167</td>
    </tr>
</table>

Desired result:

<table>
    <tr>
        <td>Column name that changed</td><td>10408</td><td>10407</td> // I am missing the 1st td
    </tr>
    <tr>
        <td>Column name that changed</td><td>168</td><td>167</td> // I am missing the 1st td
    </tr>
</table>

Answer 1

Use fetch_assoc() instead of fetch_row(). It will return an associative array. Then use foreach loop and the associative key to loop the array and compare the elements.

Also, remove store_result(), you already used get_result().

Fixed code would look like this:

$stmt = $conn->prepare("SELECT * FROM locations WHERE vstId = ?");
$stmt->bind_param("s", $vstId);
$stmt->execute();
$resultBefore = $stmt->get_result()->fetch_assoc();

// some event

$stmt = $conn->prepare("SELECT * FROM locations WHERE vstId = ?");
$stmt->bind_param("s", $vstId);
$stmt->execute();
$resultAfter = $stmt->get_result()->fetch_assoc();

foreach ($resultAfter as $col_name => $value) {
    if ($value !== $resultBefore[$col_name]) {
        $diff .= '<tr><td>' . htmlentities($col_name) . '</td><td>' . htmlentities($resultBefore[$col_name]) . '</td><td>' . htmlentities($value) . '</td></tr>';
    }
}
echo '<table>' . $diff . '</table>';