Current Location: Home> Latest Articles> Use json_encode() to convert mysqli_result to JSON format

Use json_encode() to convert mysqli_result to JSON format

M66 2025-05-28

In PHP, many times we need to get data from the database and return it in JSON format, such as providing an API interface for the front-end. To do this, you can use the mysqli extension to execute the query, and then use the json_encode() function to encode the result array into a JSON string.

This article will explain how to implement this process step by step.

1. Establish a database connection

First, you need to use mysqli to create a connection to the database:

 $servername = "localhost";
$username = "db_user";
$password = "db_password";
$database = "db_name";

$conn = new mysqli($servername, $username, $password, $database);

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

2. Execute query

Let’s take querying a table named users as an example to obtain all user data:

 $sql = "SELECT id, username, email FROM users";
$result = $conn->query($sql);

Here $result is a mysqli_result object, which contains the query results.

3. Convert query results to array

Since json_encode() cannot directly encode the mysqli_result object, we need to convert it into an array first:

 $data = array();

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
}

Here we use the fetch_assoc() method to take out each row of data as an associative array and add it to the $data array.

4. Use json_encode() to convert to JSON

Now that we have the array $data containing all the query results, we can convert it to JSON format using json_encode() :

 $jsonResult = json_encode($data);

// Output JSON data
header('Content-Type: application/json');
echo $jsonResult;

Here we also set the Content-Type header to application/json to tell the client that the JSON format is returned.

5. Complete sample code

Combining the above steps, the complete code is as follows:

 <?php
$servername = "localhost";
$username = "db_user";
$password = "db_password";
$database = "db_name";

$conn = new mysqli($servername, $username, $password, $database);

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

$sql = "SELECT id, username, email FROM users";
$result = $conn->query($sql);

$data = array();

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
}

$conn->close();

header('Content-Type: application/json');
echo json_encode($data);
?>

Tips

  • Make sure the database connection information (such as username, password, database name) is correct.

  • In actual projects, for security and performance, it is recommended to use prepared statements instead of direct splicing SQL.

  • If you want to test the interface, you can access it with a browser, for example:
    https://m66.net/api/get_users.php

This way, you can directly obtain JSON user data in the browser or front-end code.

Related