Nested prepared statements and free

Can someone explain why free_result after a while loop on a nested prepared statement causes bind_result to not update values as intended?

$stmt = $mysqli -> prepare("select col1 from table1 inner join ...");
$stmt -> bind_param("i", $id);
$stmt -> bind_result($col1);
$stmt -> execute() or die();
$stmt -> store_result();

$stmt2 = $mysqli -> prepare("select col2, col3 from table2 where col4 = ?");
$stmt2 -> bind_param("i", $col1);
$stmt2 -> bind_result($col2, $col3);

while ($stmt -> fetch()) {
    $stmt2 -> execute() or die();
    $stmt2 -> store_result();
    while ($stmt2 -> fetch()) {
        echo $col2 .",";
    }
    $stmt2 -> free_result(); // <== Works fine without this line
    echo "---";
}
$stmt -> close();
$stmt2 -> close();

If I remove free_result, I get the expected output, let's say:

1,2,---3,4,5,---6,7,---

If I leave it, the last result of the first run on the parent loop repeats like so:

1,2,---2,2,2,---2,2,---

I can't find the answer in the docs ... It seems counter-intuitive since the results are retained instead of being freed.

Here's an executable example

CodePudding user response：

As stated in the documentation for bind_result():

All columns must be bound after mysqli_stmt_execute() and prior to calling mysqli_stmt_fetch().

If you move the bind_result() call to the appropriate place it will work:

$stmt = $mysqli->prepare("select col1 from table1");
$stmt->execute();
$stmt->bind_result($col1);
$stmt->store_result();

$stmt2 = $mysqli->prepare("select col2, col3 from table2 where col4 = ?");
$stmt2->bind_param("i", $col1);

while ($stmt->fetch()) {
    $stmt2->execute();
    $stmt2->bind_result($col2, $col3);
    $stmt2->store_result();
    while ($stmt2->fetch()) {
        echo $col2 .",";
    }
    $stmt2->free_result();
    echo "---";
}
$stmt->close();
$stmt2->close();

The reason for this is that mysqli_stmt::free_result() asks mysqlnd to free up all result variables. The references are released, but neither the variables nor their values are not destroyed by PHP. They are just unbound.

You can either stop using mysqli_stmt::free_result() (which you probably should do anyway) or bind the result variables after every execution.