JSON multipul objects from MySQL

Question

I seem to be struggling to find instructions on how to generate the following JSON in PHP using MySQL

{
"mainItems": [
    {
        "id": "1",
        "title": "item 1",
        "relatedItems": [
            {
                "id": "1",
                "main_id": "1",
                "relatedTitle": "Related item 1"
            },
            {
                "id": "2",
                "main_id": "1",
                "relatedTitle": "Related item 2"
            }
        ]
    },
    {
        "id": "2",
        "title": "item 2",
        "relatedItems": [
            {
                "id": "1",
                "main_id": "2",
                "relatedTitle": "Related item 3"
            },
            {
                "id": "2",
                "main_id": "2",
                "relatedTitle": "Related item 4"
            }
        ]
    }
]

}

Say I have 2 tables

1. Main items
2. Related items

I can output a list of all the main items however i can't seem to link in the related items as an object within the main items.

Does anyone have any pointers?

Answer 1

Try to join the tables according to your needs with mySql. That way you receive the data already connected. Further you can deal with it in php like I assume you already did.