How Can I Encode MySQL Query Results as JSON in PHP?

Encoding MySQL Query Results to JSON

To encode MySQL query results into JSON format, you can utilize the json_encode() function provided by PHP. The function takes an associative array as its input and converts it into a JSON representation.

Usage

To apply json_encode() to MySQL query results, you must first fetch the results as an array. One approach is to iterate through each row of the result set and create an array of individual row arrays:

$sth = mysqli_query($conn, "SELECT ...");
$rows = array();
while ($r = mysqli_fetch_assoc($sth)) {
    $rows[] = $r;
}
print json_encode($rows);

Alternatively, if you are using a PHP version greater than or equal to 5.2 and have the php-json package installed, you can use the mysqli_fetch_all() function to retrieve the entire result set as an array:

$result = mysqli_query($conn, "SELECT ...");
$rows = mysqli_fetch_all($result, MYSQLI_ASSOC); // Assoc arrays in rows
print json_encode($rows);

Considerations

Note that applying json_encode() directly to the entire results object is not recommended, as it may result in invalid JSON output. Therefore, it is crucial to first convert the results to an array before encoding it.

The above is the detailed content of How Can I Encode MySQL Query Results as JSON in PHP?. For more information, please follow other related articles on the PHP Chinese website!