phpmysqljsonjunction-table

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?


Solution

  • 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;
    
      }