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