php - Json table inside table

Question

I want to send 3 tables in my MySQL (PDO) using json. There is a loop in the first table Note: First table (fetchAll) Second table (fetch) Third table (fetch)

$stmt1 = $db->prepare("SELECT * FROM data WHERE countid='1'");
$stmt1->execute();
$data = $stmt1->fetchAll(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;

echo json_encode($response);

Answer 1

Ok so a little more code is required now. You have to get all the data rows and then use the keys in there to get the related rows from the other 2 tables.

$stmt = $db->prepare("SELECT * FROM data WHERE countid='1'");
$stmt->execute();
$datas = $stmt->fetchAll(PDO::FETCH_OBJ);

foreach ( $datas as $key => &$data ) {
    // get related weeks data
    $stmt = $db->prepare("SELECT id,title FROM weeks WHERE data_id=:id ORDER BY id");
    $stmt->execute( array(':id'=>$data->id) );
    $data->weeks = $stmt->fetchAll(PDO::FETCH_OBJ);

    // get related user data  
    $stmt = $db->prepare("SELECT id,name FROM user WHERE id=:id ORDER BY id");
    $stmt->execute( array(':id'=>$data->user_id) );
    $data->user = $stmt->fetchAll(PDO::FETCH_OBJ);
}

echo json_encode( array('data'=>$datas) );

Results:

{
    "data": [
        {
            "id": 1,
            "title": "name of module1",
            "description": "description of module 1",
            "user_id": 1,
            "week": "1",
            "countid": 1,
            "weeks": [
                {
                    "id": 1,
                    "title": "Week 01"
                }
            ],
            "user": [
                {
                    "id": 1,
                    "name": "chris"
                }
            ]
        },
        {
            "id": 2,
            "title": "name of module 2",
            "description": "description of module 2",
            "user_id": 2,
            "week": "2",
            "countid": 1,
            "weeks": [
                {
                    "id": 2,
                    "title": "Week 02"
                }
            ],
            "user": [
                {
                    "id": 2,
                    "name": "john"
                }
            ]
        }
    ]
}