0

Here is sample code that pulls data directly from MySQL:

$mysqli = new mysqli('localhost','user','password','myDatabaseName');
$myArray = array();
if ($result = $mysqli->query("SELECT * FROM reviews")) {

    while($row = $result->fetch_array(MYSQLI_ASSOC)) {
            $myArray[] = $row;
    }
    echo json_encode($myArray);
}

$result->close();
$mysqli->close();

The problem is that Int values, such as "id", are being shown in my JSON as String values instead. Example:

[{
    "id": "1",
    "company": "Joe's Burgers",
    "rating": "Good",
    "affiliate": "0"
}]

In MySQL, id is an INT type and affiliate is a TINYINT type. How can I fix this to have properly formatted JSON? Here is what I'm expecting:

[{
    "id": 1,
    "company": "Joe's Burgers",
    "rating": "Good",
    "affiliate": 0
}]
Ethan Allen
  • 14,425
  • 24
  • 101
  • 194
  • The datatype return is a string, not an int. Cast to an int when assigning to `$myArray` – user3783243 Jun 28 '21 at 14:32
  • 2
    If you `var_dump($myArray)`, you should already see strings instead of ints, no? So the correct question is: how to properly fetch ints using mysqli? – deceze Jun 28 '21 at 14:32
  • https://stackoverflow.com/questions/1390983/php-json-encode-encoding-numbers-as-strings – Peter Jun 28 '21 at 14:32
  • 1
    Add `JSON_NUMERIC_CHECK` to your [json_encode](https://www.php.net/manual/en/function.json-encode.php), it will force the numbers as unquoted numbers – aynber Jun 28 '21 at 14:37
  • MySQL supports 64-bit integers and unsigned integers, but PHP has no data type for unsigned, and when the mysqli extension was developed, PHP had no reliable data type for 64-bit integers. So there was no way to convert all types of MySQL integers into PHP integers. Thus the extension just returns all results as strings. – Bill Karwin Jun 28 '21 at 15:31

1 Answers1

-1

Add JSON_NUMERIC_CHECK to your json_encode, it will force the numbers as unquoted numbers.

echo json_encode($myArray, JSON_NUMERIC_CHECK);
aynber
  • 22,380
  • 8
  • 50
  • 63
  • This helps to turn strings into numbers. It would be better to figure out why they're strings in the first place, when they're supposedly integers… – deceze Jun 28 '21 at 14:48
  • 1
    @deceze the MySQLi functions in PHP work with strings for all data types, if you `fetch_assoc()` (& related) an `INTEGER` database field, you'll always get a string. They don't cast values like ORMs do. – RickN Jun 28 '21 at 14:51
  • 2
    @RickN https://stackoverflow.com/a/25692758/476 – deceze Jun 28 '21 at 15:03
  • @RickN They do that only by default. You can enable automatic type casting to appropriate types, but if you use prepared statements (which everyone should) then they are returned in native types – Dharman Jun 28 '21 at 15:08