How do I properly use PHP to encode MySQL object i

Question

I am trying to iterate through a MySQL object and use an ajax call on another page to append the data but I can't get the php to return valid JSON to the callback.

This one obviously doesn't work...

<?php

    $db_host = "localhost";
    $db_user = "blah";
    $db_pass = "blah";
    $db_name = "chat";
    $mysqli = new MySQLi($db_host, $db_user, $db_pass, $db_name);
    $myQuery = "SELECT * FROM users";
    $result = $mysqli->query($myQuery) or die($mysqli->error);
    $row = $result->fetch_assoc();
    echo json_encode($row);

?>

Or this one...

<?php

    $db_host = "localhost";
    $db_user = "blah";
    $db_pass = "blah";
    $db_name = "chat";
    $mysqli = new MySQLi($db_host, $db_user, $db_pass, $db_name);
    $myQuery = "SELECT * FROM users";
    $result = $mysqli->query($myQuery) or die($mysqli->error);
    while ( $row = $result->fetch_assoc() ){
        echo json_encode($row) . ", ";
    }

?>

Answer 1

I use this:

    $json = array();

    if(mysqli_num_rows($result)){
        while($row=mysqli_fetch_assoc($result)){
                $json[]=$row;
        }
    }
    mysqli_close($mysqli);
    echo json_encode($json);

?>

and I get something like this

[ {"id":"2","usuario":"zeldafranco","password":"lol"},{"id":"3","usuario":"franco","password":"franco"},{"id":"4","usuario":"peteko","password":"sanpeteko"},{"id":"5","usuario":"prueba","password":"prueba"},{"id":"6","usuario":"test","password":"test"}, {"id":"7","usuario":"pibe","password":"hola"}, {"id":"8","usuario":"que ase","password":"que ase"},{"id":"9","usuario":"trt","password":"trt"}, {"id":"10","usuario":"tyt","password":"tyt"} ]

Answer 2

$arrUsers = array();

$fetch = mysql_query("SELECT * FROM users"); 

while ($row = mysql_fetch_array($fetch, MYSQL_ASSOC)) {



 $arrUsers['id'] = $row['name'];
    $arrUsers['col1'] = $row['col1'];
    $arrUsers['col2'] = $row['col2'];

    array_push($arrUsers,$row_array);

}

echo json_encode($arrUsers);

Answer 3

$data = array();

while ( $row = $result->fetch_assoc() ){
    $data[] = json_encode($row);
}
echo json_encode( $data );

This should do it. Also, you can use http://jsonlint.com/ to see what are the problems with your JSON output.

Update: using fetch_all() might be a good idea too

$data = $result->fetch_all( MYSQLI_ASSOC );
echo json_encode( $data );