Home >Database >Mysql Tutorial >How Can I Efficiently Encode MySQL Query Results as JSON in 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?
<?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.
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!