I dont know if this is the right place to ask this question, but I think someone of the experts on this site may give me a valuable answer.
I recently updated from PHP-7.3 to PHP-7.4 and noticed a behaviour, that I was not expecting. The following database and script are a minimal example to demonstrate my problem:
CREATE TABLE `user` (
`idUser` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL,
PRIMARY KEY (`idUser`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
INSERT INTO `user` VALUES (1,'Alice'),(2,'Eve'),(3,'Bob'),(4,'Rick');
CREATE TABLE `user_data` (
`idUserData` int(11) NOT NULL AUTO_INCREMENT,
`idUser` int(11) NOT NULL,
`data` text NOT NULL,
PRIMARY KEY (`idUserData`),
KEY `idUser` (`idUser`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
INSERT INTO `user_data` VALUES (1,1,'likes to read'),(2,3,'plays in a band');
<?php
$mysqli = new mysqli (HOST, USER, PASSWORD, DATABASE);
$stmt = $mysqli->prepare('SELECT idUser, name FROM user');
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($idUser, $name);
while ($stmt->fetch())
{
$stmt2 = $mysqli->prepare('SELECT data FROM user_data WHERE idUser = ?');
$stmt2->bind_param('i', $idUser);
$stmt2->execute();
$stmt2->store_result();
$stmt2->bind_result($data);
$stmt2->fetch();
print $name . ' => ' . $data . '<br>';
}
?>
PHP-7.3 gives me the following output:
Alice => likes to read
Eve =>
Bob => plays in a band
Rick =>
In PHP-7.4 I get this output:
Alice => likes to read
Eve => likes to read
Bob => plays in a band
Rick => plays in a band
The bind_result() or fetch() in the while loop do not clear $data on an empty sql result set. It still contains the old value from the previous iteration. In PHP-7.3 and lesser versions, the var was cleared.
So is this intended behaviour or did I stumble upon a bug?