[Solved] PDO table inside table using json

by


Start by returning objects from PDO as that is what you want.

Then only select from the table the columns that you actually need

Then build the data structure that you believe you want from the returned data

$stmt1 = $db->prepare("SELECT title,description FROM data WHERE id='1'");
$stmt1->execute();
$data = $stmt1->fetch(PDO::FETCH_OBJ);

$stmt2 = $db->prepare("SELECT id,title FROM weeks WHERE id='2'");
$stmt2->execute();
$data->weeks[] = $stmt2->fetch(PDO::FETCH_OBJ);

$stmt3 = $db->prepare("SELECT id,name FROM user WHERE id='1'");
$stmt3->execute();
$data->user[] = $stmt3->fetch(PDO::FETCH_OBJ);

$response = new stdClass();
$response->data[] = $data;

print_r($response);
echo json_encode($response);

RESULT:

stdClass Object
(
    [data] => Array
        (
            [0] => stdClass Object
                (
                    PDO table inside table using json => name of module2
                    PDO table inside table using json => description of module2
                    [weeks] => Array
                        (
                            [0] => stdClass Object
                                (
                                    [id] => 2
                                    PDO table inside table using json => Week 02
                                )

                        )

                    [user] => Array
                        (
                            [0] => stdClass Object
                                (
                                    [id] => 2
                                    [name] => john
                                )

                        )

                )

        )

)

Giving a JSON String of 

{"data":[{"title":"name of module2",
          "description":"description of module2",
          "weeks":[{"id":2,"title":"Week 02"}],
          "user":[{"id":2,"name":"john"}]
        }]
}

8

solved PDO table inside table using json