How Can I Efficiently Encode MySQL Query Results as JSON in PHP Using `json_encode()`?

JSON Encoding MySQL Results with json_encode()

json_encode() is a handy function in PHP that converts data into JSON format. For MySQL query results, there are various approaches to use this function.

Do you need to manually iterate through each row or can you apply json_encode() to the entire result object?

Using Iteration

For older PHP versions, the most straightforward method is to iterate through each row in the result set using a while loop. Here's an example:

<?php
$sth = mysqli_query($conn, "SELECT ...");
$rows = array();

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

print json_encode($rows);
?>

Note that json_encode() expects associative arrays (key-value pairs), so mysqli_fetch_assoc() fetches the rows as associative arrays.

Using mysqli_fetch_all() (Modern Approach)

Modern PHP versions have a built-in mysqli_fetch_all() function that allows you to fetch all rows in one go. This simplifies the encoding process:

<?php
$result = mysqli_query($conn, "SELECT ...");
$rows = mysqli_fetch_all($result); // list arrays with values only in rows

// Or, for associative arrays:
$rows = mysqli_fetch_all($result, MYSQLI_ASSOC);

print json_encode($rows);
?>

Additional Notes

• json_encode() requires PHP version 5.2 or later.
• The php-json package is also necessary.
• If you encounter any issues, ensure that you have enabled JSON support in your PHP configuration.

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