NOTE: This is not the same question as How do I make sure that values from MySQL keep their type in PHP? The questions here are:
- Is this behavior documented?
- What is the best method to standardize the data types of results from prepared and unprepared queries--most likely by forcing everything to string? This is, in fact, the opposite question of the one previously marked as duplicate.
Given the following table definition and values:
CREATE TABLE sample (
id int PRIMARY KEY,
note varchar(45) NULL
) ENGINE=InnoDB;
INSERT INTO sample(id, note) VALUES (1000, "A string"), (1001, NULL);
It seems that mysqli_result::fetch_* will return all field values as string|null
if the query is unprepared, whereas it will return them as string|int|null
if the query is prepared.
E.g.:
$query = "SELECT * FROM sample";
$result = $link->query($query);
var_dump($result->fetch_all());
$statement = $link->prepare($query);
$statement->execute();
$result = $statement->get_result();
var_dump($result->fetch_all());
array(2) {
[0]=>
array(2) {
[0]=>
string(4) "1000"
[1]=>
string(8) "A string"
}
[1]=>
array(2) {
[0]=>
string(4) "1001"
[1]=>
NULL
}
}
array(2) {
[0]=>
array(2) {
[0]=>
int(1000)
[1]=>
string(8) "A string"
}
[1]=>
array(2) {
[0]=>
int(1001)
[1]=>
NULL
}
}
As far as I can tell, this behavior is not documented anywhere on PHP.net, nor are there any flags available in any of the mysqli_result::fetch_* methods to set this behavior one way or the other.
Needing the hydrated arrays to be compatible between the two query methods, the best I've come up with is to cast all the members to strings as they are fetched. Am I missing something, or is this the state of mysqli?
$output = [];
while ($row = $result->fetch_assoc()) {
array_push($output, array_map(function($field) {
return (string) $field;
}, $row));
}