How to print the read php script in JSON format

[Anonymous]

Hello,
I would like to read the content of a DB, which is stored in MariaDB (Linux DB), and print it in JSON format.

I wrote the following code, and it works; however, I cannot print the output in JSON format.

Below is the code:
<?php

$hostname = "localhost";
$username = "user_cv";
$password = "";
$db = "cv";

$dbconnect=mysqli_connect($hostname,$username,$password,$db);

if ($dbconnect->connect_error) {
die("Database connection failed: " . $dbconnect->connect_error);
}

?>

<table border="1" align="center">
<tr>
<td>Reviewer Name</td>
<td>Stars</td>
<td>Details</td>
</tr>

<?php

$query = mysqli_query($dbconnect, "SELECT * FROM user_review")
or die (mysqli_error($dbconnect));

while ($row = mysqli_fetch_array($query)) {
echo
"<tr>
<td>{$row['reviewer_name']}</td>
<td>{$row['star_rating']}</td>
<td>{$row['details']}</td>
</tr>\n";

}

echo json_encode($query);

?>

Please help me fix my code, and how do I print the value in JSON format. I also wanted to print it in both a table format and JSON format to show the difference.

Thanks

 

[Anonymous]

You need to store the results of your query to an array so you can output it twice; a quick way to do this is with mysqli_fetch_all. Replace the last while loop in your code with the following:

// Read entire query result into single array
$output = mysqli_fetch_all($query, MYSQLI_ASSOC);

// Instead of using WHILE to loop over the query result, you can use FOREACH to loop over the returned array
foreach ($output as $row) {
echo
"<tr>
<td>{$row['reviewer_name']}</td>
<td>{$row['star_rating']}</td>
<td>{$row['details']}</td>
</tr>\n";

}

// $output is now the entire query result as an array, so json_encode will work
echo json_encode($output);

 

[Anonymous]

$data= mysqli_fetch_all($query, MYSQLI_ASSOC);

output($data);

/*
 * After converting data array to JSON send back to javascript using
 * this function.
 */
function output($output)
{
    http_response_code(200);
    try {
        echo json_encode($output, JSON_THROW_ON_ERROR);
    } catch (JsonException) {
    }
}

Send the data back using JSON and don't forget to parse the data if you are not using FETCH. The whole point of using AJAX(Fetch) is to have it asynchronous so the user doesn't have to reload the page.

 

[Anonymous]

Thanks a lot guys.