How to producr json in php with nested one array within multiple array(for matching values)

Question

I have two tables like below, they show the table structure as we have in our table.

table1

id    comment
1     abc
2     xyz
3     pqr

table2

id1   table1ID     reply
1      1           efg
2      1           mnr
3      2           slq

Here I want to send data as JSON as follows

 <?php 
        $ID = $req->id;
        try {
        $sql= "SELECT table1.*, table2.* FROM table1 LEFT JOIN table2 ON table1.id = table2.table1ID WHERE id = '".$ID."'";
        $res=  $connection->query($sql);
            while($row = $res->fetch(PDO::FETCH_ASSOC)) {
                    $lclData1[] = array(
                      "id" => $row["id"],
                      "comment" => $row['comment'],
                        "reply" => array(
                                    "id1" => $row['id1'],
                                    "table1ID" => $row['table1ID'],
                                    "reply" => $row['reply'],
                      )
                    );
                $Output["status"] = 1;
                $Output["msg"] = "comment";
                $Output["comment"] = $lclData1;
            $connection = null;
            echo json_encode($Output);
        }
    }
    catch (Exception $e) {
        echo $e->getMessage(), "\n";
    }
    ?>

Following shows result as in JSON representation(I need to produce like below) OUTPUT NEEDED.

{
 "status": 1,
 "message": "data",
 "comment": [
    {
        "id": "1",
        "comment": "abc",
        "reply":
        [
          {
            "id1": 1,
            "table1ID": 1,
            "reply": "efg"
        },
        {
            "id1": 2,
            "table1ID": 1,
            "reply": "mnr"
        }
      ]
    },
    {
        "id": "2",
        "comment": "xyz",
        "reply":
        [
          {
            "id1": 3,
            "table1ID": 2,
            "reply": "slq"
        }
      ]
    }
 ]
}

Here I want to produce JSON like above if one comment has more than one reply after first comment need to produce multiple replies.

Following is my output right now.

{
 "status": 1,
 "message": "data",
 "comment": [
   {
    "id": "1",
    "comment": "abc",
    "reply":
    [
      {
        "id1": 1,
        "table1ID": 1,
        "reply": "efg"
    }

  ]
},
 {
    "id": "1",
    "comment": "abc",
    "reply":
    [

    {
        "id1": 2,
        "table1ID": 1,
        "reply": "mnr"
    }
  ]
},
{
    "id": "2",
    "comment": "xyz",
    "reply":
    [
      {
        "id1": 3,
        "table1ID": 2,
        "reply": "slq"
     }
    ]
  }
 ]
}

Answer 1

You were overriding reply data in loop, hence it is showing only one record in your case,

Change your Output array as mine and check once,

while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
    $Output["status"]                         = 1;
    $Output["msg"]                            = "comment";
    $Output["comment"][$row['id']]['id']                  = $row['id'];
    $Output["comment"][$row['id']]['comment']             = $row['comment'];
    // I changed here to catch all replies in reply array
    $Output["comment"][$row['id']]['reply'][] = [
        "id1"      => $row['id1'],
        "table1ID" => $row['table1ID'],
        "reply"    => $row['reply'],
    ];
    $connection = null;
    // reset indexes
    $Output['comment'] = array_values($Output['comment']); 
    echo json_encode($Output);
}

EDIT

May be you need separate lclData1,

$lclData1 = [];
while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
    $lclData1[$row['id']]['id']      = $row['id'];
    $lclData1[$row['id']]['comment'] = $row['comment'];
    // I changed here to catch all replies in reply array
    $lclData1[$row['id']]['reply'][] = [
        "id1"      => $row['id1'],
        "table1ID" => $row['table1ID'],
        "reply"    => $row['reply'],
    ];
}
$Output["status"]  = 1;
$Output["msg"]     = "comment";
$Output["comment"] = $lclData1;
$connection        = null;
// reset indexes
$Output['comment'] = array_values($Output['comment']);
echo json_encode($Output);

Answer 2

You have to change while() code and put some lines outside it like below:

$finalData = array();
while($row = $sth->fetch(PDO::FETCH_ASSOC)) {
    $finalData[$row["id"]]['id'] =   $row["id"];
    $finalData[$row["id"]]['comment'] =  $row["comment"];
    $finalData[$row["id"]]['reply'][] =  array(
                                            "id1" => $row['id1'],
                                            "table1ID" => $row['table1ID'],
                                            "reply" => $row['reply']
                                        );

}
$Output["status"] = 1;
$Output["msg"] = "comment";
$Output["comment"]= array_values($finalData);
$connection = null;
echo json_encode($Output);

Note:- you are over-writing your array inside while() loop, as well as your code is wide-open for SQL INJECTION, so please use prepared statements of PDO

PDO::prepare

Code sample using prepared statements:

<?php 
    $ID = $req->id;
    try {
        $sql= "SELECT table1.*, table2.* FROM table1 LEFT JOIN table2 ON table1.id = table2.table1ID WHERE id = :id";
        $sth = $dbh->prepare($sql);
        $sth->execute(array(':id' =>$ID));
        $finalData = array();
        while($row = $sth->fetch(PDO::FETCH_ASSOC)) {
            $finalData[$row["id"]]['id'] =   $row["id"];
            $finalData[$row["id"]]['comment'] =  $row["comment"];
            $finalData[$row["id"]]['reply'][] =  array(
                                                    "id1" => $row['id1'],
                                                    "table1ID" => $row['table1ID'],
                                                    "reply" => $row['reply']
                                                );

        }
        $Output["status"] = 1;
        $Output["msg"] = "comment";
        $Output["comment"]= array_values($finalData);
        $connection = null;
        echo json_encode($Output);
    }
}
catch (Exception $e) {
  echo $e->getMessage(), "\n";
}
?>