Convert data from db to JSON using php

问题:

I have already seen many questions but nothing has helped. I want to convert my data from database (MySQL) to JSON using PHP. This is my PHP code:

init.php

<?php
$db_name = "webappdb";
$mysql_user = "root";
$mysql_pass = "root";
$server_name = "localhost";
$charset= "utf8";

$con = mysqli_connect($charset, $server_name, $mysql_user, $mysql_pass, $db_name);
?>

listViewBooks.php

<?php

include("init.php");

header('Content-Type: application/json');

// get all items from user_info_book table
$sql = mysqli_query("SELECT * FROM `user_info_book`");

$res = mysqli_query($con,$sql);

$result = array();

while($row = mysqli_fetch_array($res)){
    $output[] = $row;
}

echo json_encode($output);
echo json_last_error();

mysqli_close($con);

?>

The error is 0, so it's nothing.

回答1:

There are a bunch of problems in your code. For starters, you have this:

$sql = mysqli_query("SELECT * FROM `user_info_book`");

$res = mysqli_query($con,$sql);

$sql is a mysqli_result object on success or boolean false on failure. Here, it's false because you didn't pass the database link ($con). See the docs. You shouldn't, don't need to, and can't store the result of mysqli_query in a variable ($sql) and then pass that variable in another call to mysqli_query. Just do:

$sql = "SELECT * FROM `user_info_book`";

$res = mysqli_query($con, $sql);

Also, you initialize one array, then add to another:

$result = array();

while($row = mysqli_fetch_array($res)){
    $output[] = $row;
}

Perhaps you mean to do $output = array();?

You would benefit from using an IDE like PHPStorm.

回答2:

So you execute a query and assign the result to $sql:

$sql = mysqli_query("SELECT * FROM `user_info_book`");

But then you query again and use the $sql result as if it were a string:

$res = mysqli_query($con,$sql);

Probably more what you were thinking:

$sql = "SELECT * FROM `user_info_book`";
$res = mysqli_query($con,$sql);

You should use error reporting when developing:

error_reporting(E_ALL);
ini_set('display_errors', '1');

回答3:

You code is bad:

$sql = mysqli_query("SELECT * FROM `user_info_book`");
                    ^---missing DB handle
 ^---query result handle

So $sql becomes boolean false for failure, because you didn't call the query function correctly.

You then blindly use this boolean false as if it was a query string:

$res = mysqli_query($con,$sql);
                           ^---boolean false, due to previous errors

So basically, you assumed your code was perfect, and could never ever possibly have any problems, so failed to add any error handling. Since you have no error handling, you utterly ignored the errors that DID occur.

Never EVER assume success - this code is a perfect example of WHY. Your sql is syntactically perfect, yet it failed because you didn't call the query function properly.

Always assume failure, check for failure, and treat success as a pleasant surprise.

回答4:

You initialize an array called $result but try to use an array with the $output identifier which has not been initialized, PHP will not auto-initialize an array variable for you. That is where one error comes from.

The second error I noticed is:

mysqli_query("SQL")

You forgot to pass in the database connection resource as it should be:

mysqli(db_connection, sql_query)

Fix those then if you need more assistance, comment below.

Have a good day.