将PHP MySQL查询响应编码为JSON问题

I am basically trying to call a MySQL Query and get the response from the Server, which I have done and it works. Now I need to encode the response as a JSON Array (Each Row as a JSON Object and add them to a JSON Array)

My Code:

foreach ($db->query('SELECT * from mydb.UserTable') as $sqlresp) {
        $rows = array();
        while($r = mysqli_fetch_assoc($sqlresp)) {
              $rows['users'][] = $r;
        }
        print json_encode($rows);
}

The above code gives me this error:

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, array

It points to this line:

while($r = mysqli_fetch_assoc($sqlresp)) {

JSON Object I need in the JSON Array:

Roughly this would be the structure..

user
{
     name: "john",
     picture: "http://...."
     details {
          email: "email@gmail.com",
          telephone: "3456..."
     }
}

*Column names of the table are the same as the Object Property names (e.g.: name, picture, etc)

What's wrong with my code and how do I properly encode my query response's rows as a JSON Array, folks?

EDIT:

I need the Array because I need to send this array to a Javascript function. So I need it in JSON Array Format

what is $db, if it is mysqli connection resource, then it will support

IMO,

$sqlresp=$db->query('SELECT * from mydb.UserTable');

it is custom library and it returns array

so you can use

echo json_encode( $sqlresp );

I would suggest that having the query in a loop like that is going to cause the issue, perhaps a more traditional approach like this might help?

$sqlresp=$db->query('SELECT * from mydb.UserTable');
if( $sqlresp ){

    $rows = array();

    while( $r = mysqli_fetch_assoc( $sqlresp ) ) {
          $rows['users'][] = $r;
    }
    print json_encode( $rows );
}

The below works for me so perhaps it has something to do with your db connection object?

$dbhost =   'localhost';
$dbuser =   'root'; 
$dbpwd  =   'xxx'; 
$dbname =   'experiments';

$db =   new mysqli( $dbhost, $dbuser, $dbpwd, $dbname );

$result=$db->query('select * from `users` limit 10');
if( $result ){

    $rows=array();
    while( $rs = mysqli_fetch_assoc( $result ) ){
        $rows['users'][]=$rs;
    }

    print json_encode( $rows );
}

outputs ( only 3 users in test table ):

{"users":[
{"id":"1","user":"Tom","role":"3","UserID":"65f185ec6bd47af8f082f8196d0b4d24","Username":"tommy","Password":"knickers"},
{"id":"2","user":"Joe","role":"3","UserID":"d6ba0682d75eb986237fb6b594f8a31f","Username":"joey","Password":"knockers"},
{"id":"3","user":"Fred","role":"3","UserID":"eda56def9e82a3936a75aff3f4e66330","Username":"freddy","Password":"knackers"}
]}