How to get multiple tables and show them in JSON

I have these tables in MySql

Countries -> countryId , countryName
Cities -> cityId , cityName , countryId
Neighborhoods -> neighborhoodId , neighborhoodName , cityId

The output should be like this

{
  "Countries": [
    {
      "countryId": "...",
      "countryName": "..."
    },
    {
      "countryId": "...",
      "countryName": "..."
    },
    {
      "countryId": "...",
      "countryName": "..."
    },
    {
      "countryId": "...",
      "countryName": "..."
    }
  ],
  "Cities": [
    {
      "cityId": "...",
      "cityName": "...",
      "countryId": "..."
    },
    {
      "cityId": "...",
      "cityName": "...",
      "countryId": "..."
    },
    {
      "cityId": "...",
      "cityName": "...",
      "countryId": "..."
    },
    {
      "cityId": "...",
      "cityName": "...",
      "countryId": "..."
    }
  ],
  "Neighborhoods": [
    {
      "neighborhoodId": "...",
      "neighborhoodName": "...",
      "cityId": "..."
    },
    {
      "neighborhoodId": "...",
      "neighborhoodName": "...",
      "cityId": "..."
    },
    {
      "neighborhoodId": "...",
      "neighborhoodName": "...",
      "cityId": "..."
    },
    {
      "neighborhoodId": "...",
      "neighborhoodName": "...",
      "cityId": "..."
    }
  ]
}

This code gives me data of one table only

<?php
$connection = new mysqli('...', '...', '...', '...');

if ($connection->connect_errno)
{
    printf("Failed to connect to database");
    exit();
}

$result = $connection->query("SELECT * FROM Countries");
$dbdata = array();
while ($row = $result->fetch_assoc())
{
    $dbdata[] = $row;
}
echo json_encode($dbdata);
?>

I’m new with PHP so I hope someone shows me how can I do it like above. Thank You……………………………………………………………………………………………………………………

Answer

Please check the code below, I added explaination as comments to the code itself.

<?php
    // connect your database   
    $conn = mysqli_connect('*****','('*****','('*****','('*****');
    
    // select the data from the 3 tables
    $country_res = mysqli_query($conn, 'select * from Countries');
    $city_res = mysqli_query($conn, 'select * from Cities');
    $nighbor_res = mysqli_query($conn, 'select * from Neighborhoods');

    // create 3 empty arrays to avoid notices, in case of 
    // empty results from database
    $countries = [];
    $cities = [];
    $neighborhoods = [];

    // fill countries array
    while($country = mysqli_fetch_assoc($country_res)) {
        $countries[] = $country;
    }
    // fill cities array
    while($city = mysqli_fetch_assoc($city_res)) {
        $cities[] = $city;
    }
    // fill neighborhoods array
    while($nighbor = mysqli_fetch_assoc($nighbor_res)) {
        $neighborhoods[] = $nighbor;
    }

    // print your results in the format you mentioned 
    //(you have to use HTML code in order for the browser to
    // display it the way you need, so I used <pre> tag.)
    echo '<pre>';
    echo json_encode(['Countries' => $countries, 
                      'Cities' => $cities, 
                      'Neighborhoods' => $neighborhoods]);
    echo '</pre>';
?>

Advertisement

Answer