Echo nested JSON array from related database tables?

Question

I have two database tables. They are related with land_contract_annual_price.land_contract_id -> land_contract.land_contract_id.

Table 'land_contract'

Table 'land_contract_annual_price'

When I read from land_contract, I want to echo a nested JSON array like this:

[  
 {  
  "land_contract_id":118,
  "land_contract_name":"Avtalsnamn",
  "location_id":71,
  "land_contract_link":"",
  "land_contract_notes":"",
  "land_owner_id":2,
  "land_contract_start_date":"2019-07-25",
  "land_contract_end_date":"2023-07-25",
  "land_contract_terminated":"false",
  "land_contract_payment_interval":"Halv\u00e5rsvis",
  "land_contract_price_type":"R\u00f6rligt \u00e5rspris",
  "land_contract_fixed_annual_price":null,
  "land_contract_annual_prices":[  
    {"year":1, "price":873.00},
    {"year":2, "price":77289.00},
    {"year":3, "price":8.00},
    {"year":4, "price":0.00},
    {"year":5, "price":8729.00}
  ]
 }
]

I'm using prepared statements with PDO to communicate with my database. This is the query I have right now:

if (isset($_POST["land_contract_id"])){
  $stmt = $pdo->prepare("SELECT * FROM land_contract AS lo
                         LEFT JOIN land_contract_annual_price AS loi
                         ON loi.land_contract_id = lo.land_contract_id
                         WHERE lo.land_contract_id = ?");
  $stmt->execute([$land_contract_id]);
} else {
  $stmt = $pdo->prepare("SELECT * FROM land_contract");
  $stmt->execute();
}

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  $arr[] = $row;
}

if(!$arr) exit('No rows');
echo json_encode($arr);

$stmt = null;

That gives me the JSON below, which is not what I want. How can I structure my PHP to get the JSON array above?

[
 {
  "land_contract_id":127,
  "land_contract_name":"Avtalsnamn",
  "location_id":71,
  "land_contract_link":"",
  "land_contract_notes":"",
  "land_owner_id":2,
  "land_contract_start_date":"2019-07-25",
  "land_contract_end_date":"2023-07-25",
  "land_contract_terminated":"false",
  "land_contract_payment_interval":"Halv\u00e5rsvis",
  "land_contract_price_type":"R\u00f6rligt \u00e5rspris",
  "land_contract_fixed_annual_price":null,
  "land_contract_annual_price_id":1,
  "land_contract_annual_price_year":1,
  "land_contract_annual_price_amount":"873.00"
},
{
  "land_contract_id":127,
  "land_contract_name":"Avtalsnamn",
  "location_id":71,
  "land_contract_link":"",
  "land_contract_notes":"",
  "land_owner_id":2,
  "land_contract_start_date":"2019-07-25",
  "land_contract_end_date":"2023-07-25",
  "land_contract_terminated":"false",
  "land_contract_payment_interval":"Halv\u00e5rsvis",
  "land_contract_price_type":"R\u00f6rligt \u00e5rspris",
  "land_contract_fixed_annual_price":null,
  "land_contract_annual_price_id":2,
  "land_contract_annual_price_year":2,
  "land_contract_annual_price_amount":"77289.00"
},
{
  "land_contract_id":127,
  "land_contract_name":"Avtalsnamn",
  "location_id":71,
  "land_contract_link":"",
  "land_contract_notes":"",
  "land_owner_id":2,
  "land_contract_start_date":"2019-07-25",
  "land_contract_end_date":"2023-07-25",
  "land_contract_terminated":"false",
  "land_contract_payment_interval":"Halv\u00e5rsvis",
  "land_contract_price_type":"R\u00f6rligt \u00e5rspris",
  "land_contract_fixed_annual_price":null,
  "land_contract_annual_price_id":3,
  "land_contract_annual_price_year":3,
  "land_contract_annual_price_amount":"8.00"
},
{
  "land_contract_id":127,
  "land_contract_name":"Avtalsnamn",
  "location_id":71,
  "land_contract_link":"",
  "land_contract_notes":"",
  "land_owner_id":2,
  "land_contract_start_date":"2019-07-25",
  "land_contract_end_date":"2023-07-25",
  "land_contract_terminated":"false",
  "land_contract_payment_interval":"Halv\u00e5rsvis",
  "land_contract_price_type":"R\u00f6rligt \u00e5rspris",
  "land_contract_fixed_annual_price":null,
  "land_contract_annual_price_id":4,
  "land_contract_annual_price_year":4,
  "land_contract_annual_price_amount":"0.00"
},
{
  "land_contract_id":127,
  "land_contract_name":"Avtalsnamn",
  "location_id":71,
  "land_contract_link":"",
  "land_contract_notes":"",
  "land_owner_id":2,
  "land_contract_start_date":"2019-07-25",
  "land_contract_end_date":"2023-07-25",
  "land_contract_terminated":"false",
  "land_contract_payment_interval":"Halv\u00e5rsvis",
  "land_contract_price_type":"R\u00f6rligt \u00e5rspris",
  "land_contract_fixed_annual_price":null,
  "land_contract_annual_price_id":5,
  "land_contract_annual_price_year":5,
  "land_contract_annual_price_amount":"8729.00"
 }
]

Logic for @potiev:

if (isset($_POST["land_contract_id"]) {

  // Get data from land_contract where land_contract_id is $land_contract_id. Store as $landContract.

  if (land_contract_price_type == 'Rörligt årspris') {

    // Get additional data from land_contract_annual_price where land_contract_id is $land_contract_id
    // Attach land_contract_annual_prices to $landContract
    // Echo $landContract as JSON

  } else if (land_contract_price_type == 'Fast årspris') {

    // Echo $landContract as JSON

  }
} else {

  // Get all land contracts in land_contract

  For every land contract() {

    if (land_contract_price_type == 'Rörligt årspris') {

      // Get additional data from land_contract_annual_price
      // Attach land_contract_annual_prices to land contract

    } 

    Echo all land contracts as JSON

  }


}

i suggest you get the result without LEFT JOIN otherwise its difficult to fetch other table columns — devpro
– devpro, Commented Jul 25, 2019 at 14:38

potiev · Accepted Answer · 2019-07-25 15:53:39Z

1

You can divide your query in two parts.

if (isset($_POST["land_contract_id"])){
    $stmt = $pdo->prepare("SELECT * FROM land_contract WHERE land_contract_id = ?");
    $stmt->execute([$land_contract_id]);
    $landContract = $stmt->fetch(PDO::FETCH_ASSOC);
    if (isset($landContract)) {

        if ($landContract['land_contract_price_type'] == 'Rörligt årspris') {
            $landContract['land_contract_annual_prices'] = [];
            $stmt = $pdo->prepare("SELECT land_contract_annual_price_year as year, land_contract_annual_price_amount as price FROM land_contract_annual_price WHERE land_contract_id = ?");
            $stmt->execute([$land_contract_id]);
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $landContract['land_contract_annual_prices'][] = $row;
            }
        }

        $arr[] = $landContract;
    }
} else {
    $stmt = $pdo->prepare("SELECT * FROM land_contract");
    $stmt->execute();
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $arr[] = $row;
    }
}


if(!$arr) exit('No rows');
echo json_encode($arr);

$stmt = null;

edited Jul 25, 2019 at 15:53

answered Jul 25, 2019 at 14:49

potiev

5862 silver badges12 bronze badges

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

13 Comments

Rawland Hustle Over a year ago

I'm so sorry, I forgot to say that I don't know if a given land_contract has any rows in land_contract_annual_prices. The way to check that is if land_contract_price_type has the value of "Rörligt årspris" (I don't know why it turns up as "R\u00f6rligt \u00e5rspris" in my example).

devpro Over a year ago

use html entity decode for this issue @RawlandHustle

potiev Over a year ago

I added checking land_contract_price_type before selecting prices from database

Rawland Hustle Over a year ago

Thanks, it's almos there! Look at the array land_contract_annual_prices in the reference JSON file in my question. I don't want to get land_contract_annual_price_id or land_contract_id from land_contract_annual_price . Also, land_contract_annual_price_year should be presented as a property name, like "year 1", and land_contract_annual_price_amount should be the property value.

Rawland Hustle Over a year ago

Or, land_contract_annual_price_year and land_contract_annual_price_amount can be divided like this, it does not matter: {"year":1, "price":873.00}. That's actually better!

|

Collectives™ on Stack Overflow

Echo nested JSON array from related database tables?

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related