How to convert mysqli result to JSON?

Here's how I made my JSON feed:

$mysqli = new mysqli('localhost', 'user', 'password', 'myDatabaseName');
$myArray = array();
if ($result = $mysqli->query("SELECT * FROM phase1")) {
    $tempArray = array();
    while ($row = $result->fetch_object()) {
        $tempArray = $row;
        array_push($myArray, $tempArray);
    }
    echo json_encode($myArray);
}

$result->close();
$mysqli->close();

As mentioned, json_encode will help you. The easiest way is to fetch your results as you already do it and build up an array that can be passed to json_encode.

Example:

$json = array();
while($row = $stmt->fetch()){
  $json[]['foo'] = "your content  here";
  $json[]['bar'] = "more database results";
}
echo json_encode($json);

Your $json will be a regular array with each element in it's own index.

There should be very little changed in your above code, alternativly, you can return both XML and JSON since most of the code is the same.

There is one essential thing about JSON - the data must be UTF-8 encoded. Therefore, the proper encoding must be set for the database connection.

The rest is as silly as any other database operation

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$db = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_NAME);
$db->set_charset('utf8mb4');

$sql = 'SELECT DISTINCT title FROM sections ORDER BY title ASC';
$data = $db->query($sql)->fetch_all(MYSQLI_ASSOC);
echo json_encode($data);

Simply create an array from the query result and then encode it

$mysqli = new mysqli('localhost','user','password','myDatabaseName');
$myArray = array();
$result = $mysqli->query("SELECT * FROM phase1");
while($row = $result->fetch_assoc()) {
    $myArray[] = $row;
}
echo json_encode($myArray);

output like this:

[
    {"id":"31","name":"product_name1","price":"98"},
    {"id":"30","name":"product_name2","price":"23"}
]

If you want another style, you can change fetch_assoc() to fetch_row() and get output like this:

[
    ["31","product_name1","98"],
    ["30","product_name2","23"]
]