MySQLI query result (multi-lines) to JSON in PHP

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

There is no way that the code you claim is working could possibly produce the output you are suggesting it does. Can you please update with your actual code? — Sean Bright, Jul 03 '18 at 18:41
I don't understand this code `$bilan[$ligne['User']] = ...` is strange because `$ligne['User']` is not set to a value, it is not initialised. — PaulH, Jul 03 '18 at 18:42

PaulH · Answer 1 · 2018-07-03T19:06:04.387

$ligne['User'] is not initialised, could you try this:

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

I tested it with this code

$result[] = ['User_ID' => 1, 'User_Nom' => 'Nom1'];
$result[] = ['User_ID' => 2, 'User_Nom' => 'Nom2'];
$result[] = ['User_ID' => 3, 'User_Nom' => 'Nom3'];
$bilan = [];
while ($rowr = array_pop($result)) {
    $ligne = array (
        "User_ID" => $rowr['User_ID'],
        "User_Nom" => $rowr['User_Nom']
    );
    array_push($bilan, $ligne);
}
echo json_encode($bilan, JSON_FORCE_OBJECT);

It provides this result:

{"0":{"User_ID":3,"User_Nom":"Nom3"},"1":{"User_ID":2,"User_Nom":"Nom2"},"2":{"User_ID":1,"User_Nom":"Nom1"}}

Note the result has a different start, it does not contain

"":null,

any more. This was the result of the strange $bilan[undefined] = undefined line

Anthony Rutledge · Answer 2 · 2018-07-04T00:07:18.387

Converted to PHP >= 5.5 for clarity.

I am guessing and making assumptions, but what else can I do? The main issue I see is that you may be tripping over your feet with the array syntax. It appears that you want to re-index the results by 'User_ID', which I assume is some string identifier found in each table record.

Modularized, in the procedural form ...

/*
    Assuming you are attempting to re-index by the 'User_ID' field
    of each record before encoding as JSON.
*/

function getDb($ip, $user, $password, $database) {
    $db = mysqli_connect($ip, $user, $password, $database);

    //error checking etc ...

    return $db;
}

function selectRecords(mysqli $db, $sql) {
    $result = mysqli_query($db, $sql);

    if (!$result) {
        throw new UnexpectedValueException("Database query (read) was unsuccessful!");
    }

    return $result;
}

function getUserRecords(mysqli $db) {
    $query = 'SELECT * FROM Users';
    return selectRecords($db, $query);
}

function reindexByField($newIndex, $userResults) {
    $reindexed = [];

    while ($row = mysqli_fetch_assoc($userResults)) {
        if (!isset($row[$newInded])) {
            throw new OutofBoundsException("The index '" . $newIndex . "' does not exist in the tested record");
        }

        $redindexed[$row[$newIndex]] = $row;
    }

    return $reindexed;
}

function getJsonFromArray(array $records) {
    $json = json_encode($records, JSON_FORCE_OBJECT);

    if (!$json) {
        throw new UnexpectedValueException("Records were not encoded into a JSON formatted string. Got boolean false, instead.");
    }

    return $json;
}

In the procedural form, then ...

try {
    $db = getDb($ip, $user, $password, $db); // Just pretend for a moment.
    echo getJsonFromArray(reindexByField('User_ID', getUserRecords($db));
} catch (e) {
    // Your handler code here.
} finally {
    mysqli_close($db);
}

An object-oriented approach can make your code more organized.

Arnaud Sauvaget · Accepted Answer · 2018-07-04T07:20:55.067

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);

MySQLI query result (multi-lines) to JSON in PHP

3 Answers3