php - How to convert mysqli result to JSON?

Question

926 votes

6 answers

php - How to convert mysqli result to JSON?

Get the solution ↓↓↓

Undefined answered · Answer 1 · 2022-11-15

Answer

Solution:

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"]
]

Undefined answered · Answer 2 · 2022-11-15

Answer

Solution:

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

Undefined answered · Answer 3 · 2022-11-15

Answer

Solution:

As mentioned, 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 tojson_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.

Undefined answered · Answer 4 · 2022-11-15

Answer

Solution:

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

Undefined answered · Answer 5 · 2022-11-15

Answer

Solution:

I managed to run this code:

<?php
//create an array
$emparray = array();
while ($row = mysqli_fetch_assoc($result)) {
    $emparray[] = $row;
}
return json_encode($emparray);

Undefined answered · Answer 6 · 2022-11-15

Answer

Solution:

If you have mysqlnd extension installed + enabled in php, you can use:

$mysqli = new mysqli('localhost','user','password','myDatabaseName');

$result = $mysqli->query("SELECT * FROM phase1");

//Copy result into a associative array
$resultArray = $result->fetch_all(MYSQLI_ASSOC);

echo json_encode($resultArray);

mysqli::fetch_all() needs mysqlnd driver to be installed before you can use it.

Popular technologies

Latest questions:

php - How to convert mysqli result to JSON?

Answer

Answer

Answer

Answer

Answer

Answer