How can I fetch results from a junction table and insert them as an array into a JSON object using PHP?

I have Articles with Categories in a mysql Database. I would like to print out all Articles with their corresponding Categories as JSON in PHP to fetch with my Vue-App.

I’m working with the following tables: Articles, Categories and Article_has_Category (junction table, many to many):

Articles

"ID" | "Title"
------------
1    | First
2    | Second


Categories

"ID" | "Category"
------------
1    | Lifestyle
2    | Webtech


Article_has_Categories

"ID" | "Article_ID" | "Category_ID"
--------------------------------------
1    |     1        |     1    
2    |     1        |     2    

The following PHP-Code selects and prints all Articles for my Frontend to fetch:

$stmt = $pdo->prepare("SELECT * FROM Articles;");

$stmt->bindParam(':param');

if ($stmt->execute()) {

  $array = $stmt->fetchAll();

  $jsonArray = json_encode($array);

  print_r($jsonArray);

}

Printed JSON-Output:

[
{"ID":"1","Title":"First"},
{"ID":"2","Title":"Second"}
]

Is it somehow possible to insert all Categories as an array into that JSON-Output?

Desired JSON-Output:

[
{"ID":"1","Title":"First", "Categories": "[Lifestyle, Webtech]" },
{"ID":"2","Title":"Second", "Categories": "[]"}
]

Currently I’m building the desired object in my frontend first using “SELECT * FROM Articles;” to fetch all articles and then in a seperate call, fetching the corresponding categories by Article ID using the statement below:

SELECT c.Category
FROM article_has_category ac 
INNER JOIN Categories c ON c.ID = ac.Category_ID
WHERE ac.Article_ID = :id;

Is there any solution combining the two statements and building the desired object directly in my PHP File?

Answer

Okay I solved this by assembling my own JSON in PHP, instead of using json_encode().

My code is not very pretty but I commented it a bit for you to understand:

<?php

$stmt = $pdo->prepare("

SELECT * FROM Articles;

");

$stmt_categories = $pdo->prepare("

SELECT c.Category
FROM article_has_category ac
INNER JOIN Categories c ON c.ID = ac.Category_ID
WHERE ac.Article_ID = :id;

");

if ($stmt->execute()) {

  $result = $stmt -> fetchAll();

  // count items in result, in order to determine later which is the last one
  $numItems = count($result);
  $i = 0;

  // prepare the json-array to print out in the php file
  $printArray = '[';

  // for each article, run the second sql-statement using the article-ID
  foreach( $result as $row ) {

    $stmt_categories->bindParam(':id', $row['ID']);

    // executing the second statement
    if ($stmt_categories->execute()) {

      $result_category = $stmt_categories -> fetchAll();

      // save the fetched categories into a new array, using the function makeArray()
      $categories = makeArray($result_category);

      // build the json object by hand, no more need for json_encode()
      $element = '{';

        $element .= ' "ID": ';
        $element .= '"' . $row['ID'] . '",';

        $element .= $categories;

        $element .= ' "Title": ';
        $element .= '"' . $row['Title'] . '"';

        $element .= '}';

        if(++$i === $numItems) {

          // if it's the last item, do nothing

        } else {

          // if not, add a comma

          $element .= ',';

        }

        // add the built element to the printArray
        $printArray .= $element;


      }
    }

    $printArray .= ']';

    // finally print the array
    print_r($printArray);


  }

  function makeArray($categoryArray){

    $category_element .= ' "Category": ';
    $category_element .= "[";

    $numCategories = count($categoryArray);
    $n = 0;

    foreach( $categoryArray as $row ) {


      $category_element .= '"' . $row['Category'] . '"';

      if(++$n === $numCategories) {

        // if it's the last item, do nothing

      } else {

        // if not, add a comma

        $category_element .= ',';

      }

    }

    $category_element .= "],";


    return $category_element;

  }