使用JSON的表格内的PDO表格 [英] PDO table inside table using json

问题描述

我想使用json在我的MySQL(PDO)中发送3个表

I want to send 3 tables in my MySQL (PDO) using json

我的桌子:

$stmt1 = $db->prepare("SELECT * FROM data WHERE id='1'");
$stmt1->execute();
$result1 = $stmt1->fetch(PDO::FETCH_ASSOC);

$stmt2 = $db->prepare("SELECT * FROM weeks WHERE id='2'");
$stmt2->execute();
$result2 = $stmt2->fetch(PDO::FETCH_ASSOC);

$stmt3 = $db->prepare("SELECT * FROM user WHERE id='1'");
$stmt3->execute();
$result3 = $stmt3->fetch(PDO::FETCH_ASSOC);

问题如何将其发送为格式:

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

推荐答案

首先从PDO返回对象，因为这正是您想要的.

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

结果:

stdClass Object
(
    [data] => Array
        (
            [0] => stdClass Object
                (
                    [title] => name of module2
                    [description] => description of module2
                    [weeks] => Array
                        (
                            [0] => stdClass Object
                                (
                                    [id] => 2
                                    [title] => Week 02
                                )

                        )

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

                        )

                )

        )

)

提供

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

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