PHP Slim RESTful API - Two Table Join Nested JSON

Question

I currently am working on a RESTful API with the PHP Slim Framework. I currently encountered the following problem:

I have to tables with an 1:n cardinality. With an GET-Call I want to get the data of the main table and nested in there all of the tables with the foreign key. Like this:
Planed Result

[{
"id":"1",
"ueberschrift":"UEBER",
"text_kurz":"Lorem ipsum...",
"kategorie":"HEAL", 
"bilder":[
    {"src":"wed1.png"},
    {"src":"wed2.png"}
 ]},......

This is how a part of my mysql database looks like Current databse

Slim PHP Code

$app->get('/rezensionen', function ($request, $response, $args) {
        $sth = $this->db->prepare("SELECT 
rezension.id,ueberschrift,text_kurz,kategorie, bild.src FROM rezension INNER 
JOIN bild ON (rezension.id=bild.rezension_id)");
        $sth->execute();
        $todos = $sth->fetchAll();
        return $this->response->withJson($todos);
});

And this i what i get:
Actual Result

[
    {
        "id": "1",
        "ueberschrift": "UEBER",
        "text_kurz": "Lorem Ipsum...",
        "kategorie": "HEAL",
        "src": "wed1.png"
    },
    {
        "id": "1",
        "ueberschrift": "UEBER",
        "text_kurz": "Lorem Ipsum...",
        "kategorie": "HEAL",
        "src": "wed2.png"
    }
]

You hopefully know a proper way to achieve this!

Thanks a lot! Yours, David

Answer 1

You can't return a relational result set with SQL so you're going to have to a bit more work to get to that output.

If you change the query to

SELECT 
    rezension.id
   ,ueberschrift
   ,text_kurz
   ,kategorie
   ,GROUP_CONCAT(bild.src) AS bilderCSV
FROM 
    rezension 
INNER JOIN bild 
    ON rezension.id = bild.rezension_id 
GROUP BY 
    rezension.id

You'll then get one record per rezension.id and a CSV of the bild.src records that relate to it.

In code you'll then need to split that CSV and assemble a data object containing the columns from the query but with the bilder value being the array of exploded CSV. An associative array is probably the easiest way to do this.

Not sure how this is done with Slim, but here's a rough idea

foreach($todos as $todo) {
    $output['id'] = $todo->id;
    $output['ueberschrift'] = $todo->ueberschrift;
    $output['text_kurz'] = $todo->text_kurz;
    $output['kategorie'] = $todo->kategorie;

    $srcs = explode(",", $todo->bilderCSV);
    foreach($srcs as $src) {
        $output['bilder'][]['src'] = $src
    }

    $outputs[] = $output
}

return $this->response->withJson($outputs);

Hope this helps.