表内的Json表 [英] Json table inside table

问题描述

我想使用json在我的MySQL(PDO)中发送3个表.第一个表中有一个循环 笔记: 第一个表(fetchAll) 第二张表(读取) 第三张表(获取)

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);

如何将其发送为这种格式:

how to send it to this format:

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

数据:ID，名称，内容，user_id，周，countid；

data: id, name, content, user_id, week, countid;

周:id，data_id，签到，签出；

weeks: id, data_id, checkin, checkout;

用户:ID，用户名，名称，姓氏；

user: id, username, name, surname;

( data.id == weeks.data_id )和( data.user_id == user.id )相同..

推荐答案

好，现在需要更多代码.您必须获取所有data行，然后使用其中的键从其他2个表中获取相关行.

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) );

结果:

{
    "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"
                }
            ]
        }
    ]
}

这篇关于表内的Json表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！