MySQLi query to join over several tables with pure relationship tables

Question

so I have this schema as shown in the image.

Now while I've been trying to figure out all day how to do this, I gotta say I haven't become a bit wiser..

What I want to know is if it is possible to get a result like this from one single query?

    {
      "activitys": [
        {
          "activity_name": "Bicycling",
          "attributes": [
            {
              "attribute_name": "Time"
            },
            {
              "attribute_name": "city",
              "options": [
                {
                  "option_name": "Stockholm"
                },
                {
                  "option_name": "Vasteras"
                }
              ]
            }
          ]
        },
        {
          "activity_name":"asdf"
          ...and so on
        }
      ]
    }

And if so, how do I?

Further more, does my schema look stupid or are there some logic to it? I'm still new at this and I feel that I might have overcomplicated or misinterpreted stuff..

Thanks on advance

Edit: After fiddeling some more I now have managed to get data that are related to each other, as follows:

    {
        "success": 1,
        "activitys": [
            {
                "activity_id": "16",
                "activity_name": "Bicycling",
                "attribute_id": "47",
                "attribute_name": "City",
                "option_id": "50",
                "option_name": "Stockholm"
            },
            {
                "activity_id": "16",
                "activity_name": "Bicycling",
                "attribute_id": "47",
                "attribute_name": "City",
                "option_id": "51",
                "option_name": "Vasteras"
            },
            {
                "activity_id": "16",
                "activity_name": "Bicycling",
                "attribute_id": null,
                "attribute_name": "Duration",
                "option_id": null,
                "option_name": null
            },
            {
                "activity_id": "16",
                "activity_name": "Bicycling",
                "attribute_id": "49",
                "attribute_name": "Bike",
                "option_id": "52",
                "option_name": "Grandmas old bike"
            },
            {
                "activity_id": "16",
                "activity_name": "Bicycling",
                "attribute_id": "49",
                "attribute_name": "Bike",
                "option_id": "53",
                "option_name": "My superfast bike"
            },
            {
                "activity_id": "16",
                "activity_name": "Bicycling",
                "attribute_id": "49",
                "attribute_name": "Bike",
                "option_id": "54",
                "option_name": "My childhood bike"
            },
            {
                "activity_id": "16",
                "activity_name": "Bicycling",
                "attribute_id": "49",
                "attribute_name": "Bike",
                "option_id": "55",
                "option_name": "Pablos bike"
            },
            {
                "activity_id": "16",
                "activity_name": "Bicycling",
                "attribute_id": null,
                "attribute_name": "Distance",
                "option_id": null,
                "option_name": null
            },
            {
                "activity_id": "17",
                "activity_name": "Running",
                "attribute_id": null,
                "attribute_name": "Duration",
                "option_id": null,
                "option_name": null
            }
        ]
    }

However as you can see, for some reason the attribute_id for those attributes that don't have any options didn't come along.. I guess when I've sorted that out I just have to come up with some algorithm in order to format my response as expected (as the JSON string in my original post), since that seems impossible to achieve with the rows that mysqli returns.

Here's my code:

    if (isset($_POST['get_activitys'])) {
        $records = array();

        $sql = "SELECT * 
                FROM activities a 
                LEFT JOIN activity_attributes aa ON a.activity_id = aa.activity_id 
                LEFT JOIN attributes at ON aa.attribute_id = at.attribute_id 
                LEFT JOIN attributes_options ao ON at.attribute_id = ao.attribute_id 
                LEFT JOIN options o ON ao.option_id = o.option_id";

        if($results = $conn->query($sql)) {
            if ($results->num_rows) {
                while ($row = $results->fetch_object()) {
                    $records[] = $row;
                }
                $results->free();

                if (!count($records)) {
                $response['success'] = 0;
                $response['message'] = "Hittade inga aktiviteter";
                die(json_encode($response));

                } else {
                    $response['success'] = 1;
                    $response['activitys'] = array();

                    foreach ($records as $r) {
                        array_push($response['activitys'], $r);
                    }
                    // Testing
                    echo "<pre>";
                    echo json_encode($response, JSON_PRETTY_PRINT);
                    echo "</pre>";
                    die();

                    die(json_encode($response));
                }           
            } else {
                $response['success'] = 0;
                $response['message'] = "Hittade inga aktiviteter";
                die(json_encode($response));
            }
        } else {
            $response['success'] = 0;
            $response['message'] = "Database query failed: (" . $conn->errno . ") " . $conn->error;
            die(json_encode($response));
        }
    }

Answer 1

No problem with your query. You need to structure the query result in nested arrays. Here's the untested code.

if (isset($_POST['get_activitys'])) {
    $records = array();
    $activity_names = array();
    $attribute_names = array();

    $sql = "SELECT * 
            FROM activities a 
            LEFT JOIN activity_attributes aa ON a.activity_id = aa.activity_id 
            LEFT JOIN attributes at ON aa.attribute_id = at.attribute_id 
            LEFT JOIN attributes_options ao ON at.attribute_id = ao.attribute_id 
            LEFT JOIN options o ON ao.option_id = o.option_id";

    if($results = $conn->query($sql)) {
        if ($results->num_rows) {
            while ($row = $results->fetch_object()) {
                $activity_names[$row->activity_id] = $row->activity_name;
                $attribute_names[$row->attribute_id] = $row->attribute_name;
                $records[$row->activity_id][$row->attribute_id][$row->option_id] = $row->option_name;
            }
            $results->free();

            if (!count($records)) {
            $response['success'] = 0;
            $response['message'] = "Hittade inga aktiviteter";
            die(json_encode($response));

            } else {
                $response['success'] = 1;
                $response['activitys'] = array();

                foreach ($records as $activity_id => $activity) {
                    $activity_obj = array('activity_name' => $activity_names[$activity_id], 'attributes' => array());

                    foreach ($activity as $attribute_id => $attributes) {
                        $attribute_obj = array('attribute_name' => attribute_names[$attribute_id], 'options' => array());

                        foreach ($attributes as $option_id => $option_name) {
                            $option_obj = array('option_name' => $option_name);
                            $attribute_obj['options'][] = $option_obj;
                        }

                        if (!count($attribute_obj['options'])) {
                            unset($attribute_obj['options']);
                        }

                        $activity_obj['attributes'][] = $attribute_obj;
                    }

                    if (!count($activity_obj['attributes'])) {
                        unset($activity_obj['attributes']);
                    }

                    $response['activitys'][] = $activity_obj;
                }
                // Testing
                echo "<pre>";
                echo json_encode($response, JSON_PRETTY_PRINT);
                echo "</pre>";
                die();

                die(json_encode($response));
            }           
        } else {
            $response['success'] = 0;
            $response['message'] = "Hittade inga aktiviteter";
            die(json_encode($response));
        }
    } else {
        $response['success'] = 0;
        $response['message'] = "Database query failed: (" . $conn->errno . ") " . $conn->error;
        die(json_encode($response));
    }
}