How to Efficiently Encode MySQL Query Results as JSON in PHP?

Json Encoding MySQL Result Sets

Query Execution and Retrieval

To retrieve data from a MySQL database and store it in a PHP variable, you can use mysqli_query():

$sth = mysqli_query($conn, "SELECT ...");

JSON Encoding of Results

To encode the query results in JSON format, you can use the json_encode() function. However, you need to iterate through the results and encode each row individually:

$rows = array();
while($r = mysqli_fetch_assoc($sth)) {
    $rows[] = $r;
}
print json_encode($rows);

Requirements for JSON Encoding

Note that json_encode() requires PHP version 5.2 or higher and the "php-json" package. If not installed, PHP will attempt to automatically include it.

Alternative Approach with mysqli_fetch_all()

In modern PHP versions, you can use the mysqli_fetch_all() function to retrieve all the query results in an array at once:

$result = mysqli_query($conn, "SELECT ...");
$rows = mysqli_fetch_all($result); // list arrays with values only in rows
// or
$rows = mysqli_fetch_all($result, MYSQLI_ASSOC); // assoc arrays in rows

print json_encode($rows);

Using mysqli_fetch_all() for JSON Encoding

The mysqli_fetch_all() function can simplify the JSON encoding process by creating the array of rows for you. Simply pass the results variable to json_encode(), and it will automatically convert the data into JSON format.

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