PHP中SQL整数字段转JSON变字符串的问题排查与解决

Hey there! Let's break down why your integer fields (Hour and Minute) are showing up as strings in your JSON output, and how to fix it.

Why this happens #

By default, the mysqli_fetch_array() function returns all values as strings, regardless of their original data type in the database. This is because the mysqli extension doesn't preserve native database types in its default fetch mode—it converts everything to strings for consistency in PHP's loosely typed environment.

Fixes you can implement #

1. Enable native integer/float retrieval (recommended)

You can tell mysqli to return integers and floats as their native PHP types by setting the MYSQLI_OPT_INT_AND_FLOAT_NATIVE option. This is the cleanest, most scalable solution:

// Initialize the connection with the option (do this BEFORE connecting)
$db = mysqli_init();
mysqli_options($db, MYSQLI_OPT_INT_AND_FLOAT_NATIVE, true);
// Replace with your actual connection details
mysqli_real_connect($db, 'your_host', 'your_user', 'your_password', 'your_db');

// If you already have an existing connection (PHP 8.1+ supports this)
// mysqli_set_option($db, MYSQLI_OPT_INT_AND_FLOAT_NATIVE, true);

$sql = "select * from defaulttime"; 
$result = mysqli_query($db, $sql); 
$row_set = [];
while ($row = mysqli_fetch_assoc($result)){ 
    $row_set[] = $row; 
} 
echo trim(json_encode($row_set));

With this setting, Hour and Minute will automatically be integers in your array, so json_encode() will output them as numeric values instead of strings.

2. Manually cast fields to integers

If you can't modify the connection settings (e.g., legacy code constraints), you can explicitly cast the integer fields in your loop:

$sql = "select * from defaulttime"; 
$result = mysqli_query($db, $sql); 
$row_set = [];
while ($row = mysqli_fetch_array($result)){ 
    // Cast the integer fields to native PHP ints
    $row['Hour'] = (int)$row['Hour'];
    $row['Minute'] = (int)$row['Minute'];
    $row_set[] = $row; 
} 
echo trim(json_encode($row_set));

This works well if you only have a few fields to handle, but it's less efficient if you have many integer columns.

3. Switch to PDO (alternative approach)

If you're open to using PDO instead of mysqli, it handles native data types more intuitively by default:

// Set up PDO connection
$pdo = new PDO(
    "mysql:host=your_host;dbname=your_db;charset=utf8mb4",
    'your_user',
    'your_password',
    [
        PDO::ATTR_STRINGIFY_FETCHES => false, // Don't convert numbers to strings
        PDO::ATTR_EMULATE_PREPARES => false
    ]
);

$stmt = $pdo->query("select * from defaulttime");
$row_set = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo trim(json_encode($row_set));

PDO will return integers as native PHP ints out of the box with these settings.

内容的提问来源于stack exchange，提问作者Shahid Miah