make output from array with same keys from the database

Question

I get the information from the database. For example, my database data is as follows: I get the information from the database. For example, my database data is as follows:

-------------------------------------------------
    p_id | description_name | description_value |
-------------------------------------------------
    1    |        author    |          X1       |
-------------------------------------------------
    1    |        editor    |          X2       |
-------------------------------------------------
    2    |        author    |          Y1       |
-------------------------------------------------
    3    |        author    |          Z1       |
-------------------------------------------------
    3    |        editor    |          Z2       |
------------------------------------------------- ...

So in the php code section I use this method to get descriptions:

<?php
    ...

    $result5 = mysqli_query($conn,"SELECT * FROM `descriptions` ");

    while ($row5 = $result5 ->fetch_assoc()){ 

        $des[] = [(int) $row5['p_id'] ,[   $row5['description_name'] => $row5['description_value']]   ]  ;      

    }
    echo json_encode( $des);

?>

this is the output of this code:

[[1,{"author": "X1"}],[1,{"editor": "X2"}],[2,{"author": "Y1"}],[3,{"author": "Z1"}],[3,{"editor": "Z2"}],[ ...

But, my expected output is, like this:

[{"p_id" : 1,"author": "X1","editor": "X2"},{"p_id" : 2, "author": "Y1"},{"p_id" : 3,"author": "Z1","editor": "Z2"},{...

I hope that with your guidance this problem will be solved for me, Thank You...

Try executing your query from phpMyAdmin or some similar tool.... or from mySql console.. to see what response will you get. It can happen that your table is not organized as you think it is. — MilanG, Jan 27 '20 at 12:54

Nigel Ren · Accepted Answer · 2020-01-27T13:15:02.413

What this code does is index your output by the p_id value, then if there is not already some data there, it just adds a new element with the p_id . It then adds in the data with the description_name and description_value elements...

In the json_encode() it uses array_values() to remove the index used to build the data...

$des = [];
while ($row5 = $result5 ->fetch_assoc()){
    if ( !isset ($des[$row5['p_id']])){
        $des[$row5['p_id']] = ["p_id" => (int)$row5['p_id']];
    }
    $des[$row5['p_id']][$row5['description_name']] = $row5['description_value'] ;

}
echo json_encode(array_values( $des ));

with some test data, this gives (plus formatting)...

[
    {
        "p_id": 1,
        "editor": "X2",
        "author": "Y1"
    },
    {
        "p_id": 2,
        "editor": "X2"
    }
]

make output from array with same keys from the database

1 Answers1