MySQLI在PHP中查询结果（多行）到JSON

Question

I can not manage to see all the lines of my mysql query result returned as JSON Object using JSON_encode() php function. Here is my code :

$Sql_Query = "SELECT * FROM Users";
$result = mysqli_query($dbc,$Sql_Query);
$ligne = array();
$bilan = array();

while ($rowr = mysqli_fetch_assoc($result)) {
    $ligne = array (
        "User_ID" => $rowr['User_ID']
    );
    $bilan[$ligne['User']] = $ligne[[
        ['User_ID'][$rowr['User_ID']]
    ]];
    array_push($bilan, $ligne);
}
echo json_encode($bilan, JSON_FORCE_OBJECT);

It returns me :

{"":null,"0":{"User_ID":"1"},"1":{"User_ID":"19"},"2":{"User_ID":"78"},"3":{"User_ID":"79"},"4":{"User_ID":"85"},"5":{"User_ID":"86"},"6":{"User_ID":"87"},"7":{"User_ID":"88"},"8":{"User_ID":"91"},"9":{"User_ID":"92"},"10":{"User_ID":"93"},"11":{"User_ID":"94"},"12":{"User_ID":"95"},"13":{"User_ID":"96"},"14":{"User_ID":"97"},"15":{"User_ID":"98"},"16":{"User_ID":"99"},"17":{"User_ID":"100"},"18":{"User_ID":"101"},"19":{"User_ID":"102"},"20":{"User_ID":"103"},"21":{"User_ID":"104"},"22":{"User_ID":"105"},"23":{"User_ID":"106"},"24":{"User_ID":"107"},"25":{"User_ID":"108"},"26":{"User_ID":"109"},"27":{"User_ID":"110"},"28":{"User_ID":"111"},"29":{"User_ID":"112"},"30":{"User_ID":"113"},"31":{"User_ID":"114"},"32":{"User_ID":"115"},"33":{"User_ID":"116"}}

Now, I am trying to associate the other fields of each record in the json output. But when adding this to me code, there is no more output.

while ($rowr = mysqli_fetch_assoc($result)) {
    $ligne = array (
        "User_ID" => $rowr['User_ID'],
        "User_Nom" => $rowr['User_Nom']
    );      
    $bilan[$ligne['User']] = $ligne[[
        ['User_ID'][$rowr['User_ID']]
    ][
        ['User_Nom'][$rowr['User_Nom']]
    ]];
    array_push($bilan, $ligne);
}
echo json_encode($bilan, JSON_FORCE_OBJECT);

It seems like on numerical values can be displayed and not alpha characters.

Please help me mixing in the same output both numerical and alpha contents.

Thanks Arnaud

Answer 1

You're right, since the orginal clean plan, because it was not working, I made too many modifications which finally added complexity where there is no need...

I found in php doc the possibility to learn more about the error generated when adding the other fields in the json conversion. json_last_error() was the key for understanding the issue. So I added :

switch (json_last_error()) {
        case JSON_ERROR_NONE:
            echo ' - Aucune erreur';
        break;
        case JSON_ERROR_DEPTH:
            echo ' - Profondeur maximale atteinte';
        break;
        case JSON_ERROR_STATE_MISMATCH:
            echo ' - Inadéquation des modes ou underflow';
        break;
        case JSON_ERROR_CTRL_CHAR:
            echo ' - Erreur lors du contrôle des caractères';
        break;
        case JSON_ERROR_SYNTAX:
            echo ' - Erreur de syntaxe ; JSON malformé';
        break;
        case JSON_ERROR_UTF8:
            echo ' - Caractères UTF-8 malformés, probablement une erreur d\'encodage';
        break;
        default:
            echo ' - Erreur inconnue';
        break;
    }

This returns me a UTF-8 encoding issue. So I modified my code adding some

utf8_encode($rowr['Fieldname'])

First working solution is very near from @PaulH one's, just, in my specific case, I definetely have to add (utf8_encode()) statement :

    $Sql_Query = "SELECT * FROM Users";
    $result = mysqli_query($dbc,$Sql_Query);
    $ligne =array();
    $bilan = array();
    while ($rowr = mysqli_fetch_assoc($result)) {
        $ligne = array ("User_ID" => $rowr['User_ID'],  
                        "User_Nom" => utf8_encode($rowr['User_Nom']),
                        "User_Prenom" =>utf8_encode($rowr['User_Prenom']));
        array_push ($bilan, $ligne);
    }
    echo json_encode($bilan, JSON_FORCE_OBJECT); 

and it now displays all the fields, all the lines. But there were still some "é" transformed to "\u00e9". So this post put the final brick to the solution.

I modified :

JSON_FORCED_OBJECT

to

JSON_UNESCAPED_UNICODE

as json_encode() parameter.

Finally, code delivering exactly what I want is the following :

$Sql_Query = "SELECT * FROM Users";
$result = mysqli_query($dbc,$Sql_Query);
$bilan = array();
while ($rowr = mysqli_fetch_assoc($result)) {
    $ligne = array ("User_ID" => $rowr['User_ID'],  
                    "User_Nom" => utf8_encode($rowr['User_Nom']),
                    "User_Prenom" =>utf8_encode($rowr['User_Prenom']));
    array_push ($bilan, $ligne);
}
echo json_encode($bilan, JSON_UNESCAPED_UNICODE);