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: