1

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:

  1. Is this behavior documented?
  2. 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));
}
Matt
  • 61
  • 8
  • 2
    The best method is described in the other answer. Use `mysqli_options($mysqli, MYSQLI_OPT_INT_AND_FLOAT_NATIVE, true);`. This is documented https://www.php.net/manual/en/mysqli.options.php but poorly I admit. – Dharman Oct 21 '21 at 22:42
  • 4
    Deleting and reposting [previous questions](https://stackoverflow.com/questions/69668396/mysqli-fetch-behavior-differs-based-on-query-preparion) to remove the 'closed' label isn't allowed. See https://stackoverflow.com/help/reopen-questions – Undo Oct 21 '21 at 22:48
  • @Dharman -- thanks for the response. I don't know who keeps marking this question as a duplicate despite me attempting to clarify that I am NOT trying to get the query results of nonprepared queries into their native data types. I am trying to force the results of parameterized queries back to strings to be compatible with legacy code. Ah, well. – Matt Oct 22 '21 at 17:36
  • 1
    That's not how people read the question. You asked two questions. *"Is this behavior documented?"* It kind of is, but badly. *"What is the best method to standardize the data types of results from prepared and unprepared queries"* the best way to standardize is to ask mysqli to cast it to int/float when not using binary protocol. If your question is how to turn integers and floats to strings then this is what you should focus your question on. However, you shouldn't delete and repost. Edit the question to explain why the provided answer is not the right one. – Dharman Oct 22 '21 at 17:39
  • Good point. Reposted the question more directly – Matt Oct 22 '21 at 18:03

0 Answers0