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

Encoding MySQL Query Results as JSON Data

Retrieving and manipulating data from a MySQL database is a common task in web development. To present this data in a structured format suitable for communication over the internet, JSON (JavaScript Object Notation) encoding is often employed. The json_encode() function in PHP provides a straightforward way to convert MySQL query results into JSON strings.

Using json_encode() with MySQL Query Results

To encode MySQL query results as JSON data, follow these steps:

1. Establish a database connection: Use the mysqli_connect() function to connect to the MySQL database.
2. Execute a query: Run a query that returns the desired data using the mysqli_query() function.
3. Fetch the results: Utilize the mysqli_fetch_assoc() function to retrieve each row as an associative array.
4. Create an array of rows: Iterate through the results, adding each row to an array.
5. Encode the array as JSON: Employ the json_encode() function to convert the array of rows into a JSON string.
6. Output the JSON string: Use the print function to display or pass the JSON data to another script or client.

Alternative Method using mysqli_fetch_all()

In modern versions of PHP, you can use the mysqli_fetch_all() function to retrieve all rows from a query as an array in one operation. This simplifies the process by eliminating the need for iteration:

$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);

Note:

• The json_encode() function requires PHP version 5.2 or later and the php-json package.
• UTF-8 character encoding is used by default. To specify a different encoding, use the JSON_UNESCAPED_UNICODE option in the json_encode() function.

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