How to nest MySQL results into a JSON array using PHP?

Question

Please see the link below... http://api.androiddeft.com/cities/cities_array.json

I'm trying to nest my results from the DB into the structure below:

[
    {
        region:"1",
        province:[{
           "Ilocos Norte",
           "Ilocos Sur"  
         }]
    },
    {
        region:"2",
        province:[{
           "Isabela",
           "Cagayan"  
         }]
    }
]

However what I have tried isn't working, I'm not able to get the results I am looking for:

$rows = array();
$rs = array();
$result = mysqli_query($connect, "SELECT regDesc, regCode FROM refregion ORDER BY regCode ASC");

while($row=mysqli_fetch_array($result)){
    $result2 = mysqli_query($connect, "SELECT provDesc FROM refprovince WHERE regCode = '".$row["regCode"]."' ORDER BY provCode ASC");      

    while($rs=mysqli_fetch_array($result2))

    $rows[] = array(
        'region' => $row["regDesc"],
        'province'=>$rs["provDesc"]
        );
    }           

echo json_encode($rows);

This instead results in the following output:

[
  {
    region: "REGION I",
    province: "ILOCOS NORTE"
  },
  {
    region: "REGION I",
    province: "ILOCOS SUR"
  },
  {
    region: "REGION II",
    province: "CAGAYAN"
  },
  {
    region: "REGION II",
    province: "ISABELA"
  }

]

you forgot?, okay i'll refresh your memory again, get the json using file_get_contents, then json_decode it (with the true flag), then treat it like any normal array, add as many as you wish. you have to do the coding though. — Kevin
– Kevin, Commented Oct 18, 2018 at 4:29
That's not valid JSON. It's also unclear what code you have, and what fixes you attempted so far. — mario
– mario, Commented Oct 18, 2018 at 4:31
I found the answer here... thanks to your answer... stackoverflow.com/questions/34668484/… — EMPaguia
– EMPaguia, Commented Oct 19, 2018 at 3:03

Darell · Accepted Answer · 2018-10-18 09:01:28Z

0

Try:

    $rows = array();
    $rs = array();
    $result = mysqli_query($connect, "SELECT regDesc, regCode FROM refregion ORDER BY regCode ASC");
    while($row=mysqli_fetch_array($result)){
        $region = $row['regDesc'];
        $result2 = mysqli_query($connect, "SELECT provDesc FROM refprovince WHERE regCode = '".$row["regCode"]."' ORDER BY provCode ASC");      

        while($rs=mysqli_fetch_array($result2)){
            array_push($province,$rs['provDesc'];
        }           

        $tempArray = array(
            'region' => $region,
            'province' => $province
        )

        array_push($rows,$tempArray);
    }

    echo json_encode($rows);

answered Oct 18, 2018 at 9:01

Darell

1

Sign up to request clarification or add additional context in comments.

1 Comment

toti08 Over a year ago

From review: could you please add some more explanation to your answer?

Collectives™ on Stack Overflow

How to nest MySQL results into a JSON array using PHP?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related