How to turn off casting behaviour of mysqli_result::fetch_assoc()?

Question

As you can see the output of PDO prepared statement vs MySQLi statement. Now the problem is that $stmt_result->fetch_assoc() is casting the numeric fields to int and float. How can this behaviour can be turned off?

As we want to move our code to MySQLi and our API is used by mobile apps so we cannot change or cast the responce fields.

=============== Using PDO ================

$stmt = $pdo->prepare($query);
$result = $stmt->execute($params);
if( $stmt->rowCount() > 0)
{
    $dataset = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
header('Content-type: application/json');
echo json_encode($dataset);

Output:

[
    {
        "isRegistered": "0",
        "DeviceOS": "Android 8.0.0",
        "isActive": "1",
        "City": "Brooklyn"
    }
]

=============== Using MySQLi ================

$stmt = $mysqli->stmt_init();
$stmt->prepare($query);
....
$stmt->execute();
$stmt_result = $stmt->get_result();
if( $stmt_result->num_rows > 0) {
    while( $row = $stmt_result->fetch_assoc() )
        $dataset[] = $row;
}
header('Content-type: application/json');
echo json_encode($dataset);

Output:

[
    {
        "isRegistered": 0,
        "DeviceOS": "Android 8.0.0",
        "isActive": 1,
        "City": "Brooklyn"
    }
]

What are the column types in sql? PDO should be casting these to integers too, but you only want strings returned? — Devon Bessemer, Sep 04 '18 at 15:10
I've tried it as well and PDO does seem to cast int columns to PHP numeric types. — apokryfos, Sep 04 '18 at 15:11
[This answer](https://stackoverflow.com/questions/5323146/mysql-integer-field-is-returned-as-string-in-php) seems to suggest that mysqli will always convert to strings — RiggsFolly, Sep 04 '18 at 15:13
@RiggsFolly That question is 7 years old, maybe it's been fixed in that time. — Barmar, Sep 04 '18 at 15:16
@Barmar Good point. Thats basically why I didnt even consider using it as a Dup — RiggsFolly, Sep 04 '18 at 15:16
@Barmar It also suggests that using the native driver can solve it [see](https://stackoverflow.com/a/25692758/2310830) — RiggsFolly, Sep 04 '18 at 15:18
@RiggsFolly That's probably it -- the OP here is probably using MYSQLND. — Barmar, Sep 04 '18 at 15:19

Devon Bessemer · Answer 1 · 2018-09-04T15:20:55.523

3

If you desire only strings in the result set, you could map every value to a string as you're building $dataset:

while( $row = $stmt_result->fetch_assoc() ) {
     $dataset[] = array_map('strval', $row);
}

edited Sep 04 '18 at 15:20

answered Sep 04 '18 at 15:15

Devon Bessemer

34,461
9
69
95

How to turn off casting behaviour of mysqli_result::fetch_assoc()?

1 Answers1