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

JSON Encoding MySQL Results with PHP

In PHP, the json_encode() function is used to convert PHP data into JSON format. This can be useful when sending data to a web application or API, as JSON is a widely supported data format.

But how do we use json_encode() with MySQL query results? Do we need to iterate through each row of the results or can we apply it to the entire results object?

Using json_encode() with MySQLi

<?php $mysqli = new mysqli("host", "username", "password", "database");

$result = $mysqli->query("SELECT * FROM table");

$rows = array();
while ($row = $result->fetch_assoc()) {
    $rows[] = $row;
}

$json_data = json_encode($rows);

echo $json_data;
?>

In this example, we first connect to the MySQL database using the mysqli class. Then, we execute a query on the database and the resulting object ($result) is stored in the $result variable.

We use the fetch_assoc() method to fetch the result rows as associative arrays ($row). We then add each associative array ($row) to the $rows array.

Finally, we use json_encode() on the $rows array to convert it into JSON format. The json_data string can be printed or used in any way you see suitable.

Using mysqli_fetch_all()

PHP versions 5.3 and above support the mysqli_fetch_all() which simplifies the above code as follows:

<?php $mysqli = new mysqli("host", "username", "password", "database");

$result = $mysqli->query("SELECT * FROM table");

$rows = $result->fetch_all(MYSQLI_ASSOC); // get associative arrays

$json_data = json_encode($rows);

echo $json_data;
?>

With mysqli_fetch_all() the rows can be retrieved in one go, saving us the loop and achieving the same result as above.

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