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....................................................................................................................................

CodePudding user response：

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>';
?>

CodePudding user response：

Try this... for multiple tables run separate queries for every table.

<?php
// DB connection
$con = mysqli_connect("***","***","***","***");
if(!$con){
    echo mysqli_connect_error();
    exit;
}
$Countries = mysqli_query($con," SELECT * from Countries");
$Cities = mysqli_query($con," SELECT * from Cities ");
$Neighborhoods = mysqli_query($con," SELECT postNumber,adCode from Neighborhoods ");

$Countries1 = array();
$Cities1 = array();
$Neighborhoods1 = array();

if($Countries && $Cities && $Neighborhoods){
        while($Countries11 = mysqli_fetch_assoc($Countries)){
        $Countries1[] = $Countries11;
     }
     while($Neighborhoods11 = mysqli_fetch_assoc($Neighborhoods)){
        $Neighborhoods1[] = $Neighborhoods11;
     }
     while($Cities11 = mysqli_fetch_assoc($Cities)){
        $Cities1[] = $Cities11;
     }
}

$array = array();
$array["Countries1"] = $Countries1;
$array["Cities1"] = $Cities1;
$array["Neighborhoods1"] = $Neighborhoods1;

echo json_encode($array);
?>

Are you looking for something like that?