How to Convert Database Data to JSON Format and Output in PHP

Preparation

Before we begin, there are a few preparations to make:

1. Make sure PHP and MySQL are installed and the database connection is configured correctly.

2. Create a MySQL database and set up a table to store the data.

3. Write PHP code to connect to the database and fetch data.

Connect to the Database and Query Data

Before we can fetch data, we need to connect to the database and perform a query. Here is the basic approach to connect to MySQL and query data using PHP:

// Connect to database
$conn = new mysqli("localhost", "username", "password", "database");

// Check connection
if ($conn->connect_errno) {
    die("Connection failed: " . $conn->connect_error);
}

// Query data
$sql = "SELECT * FROM table";
$result = $conn->query($sql);

// Close the database connection
$conn->close();

Convert Data to JSON Format

After fetching the data, we can use PHP’s json_encode() function to convert the data into JSON format. Here's how to do it:

$data = array();

// Store query results in an array
while ($row = $result->fetch_assoc()) {
    $data[] = $row;
}

// Convert the array to JSON format
$json = json_encode($data);

Output JSON Data

Once the data is converted to JSON, we need to output it to the frontend. In PHP, we can use the header() function to set the response header to indicate the content type is JSON, and then use the echo statement to output the data:

// Set response header to JSON format
header('Content-Type: application/json');

// Output JSON data
echo $json;

Now, the frontend can receive the data fetched from the database and converted to JSON format.

Summary

This article explains how to convert data from a database to JSON format and output it in PHP. The entire process includes connecting to the database, querying data, converting the data to JSON, and how to output the JSON data to the frontend. Converting data to JSON format makes it easier to interact with data and enhances the efficiency of data transmission between the frontend and backend.

Key points:

• Use json_encode() to convert data fetched from the database to JSON format.
• Set the response header with header() to specify the content type as JSON.
• Use echo to output the JSON data to the frontend.

I hope this article helps you better understand and apply data conversion and output in PHP.