MySQL:请求使用 Array 创建一个 JSON 对象 [英] MySQL : Request to make an JSON object with Array

问题描述

好的，我有一个简单的问题...我有一个包含两个表的数据库:

<块引用>

文章(id、内容、标题、日期)

评论(id_article、用户名、内容)

我想获得这样的 JSON 数组:

<预><代码>[{"id": "5785634a87c5a0075bf211de","title": "艾丽塔","content": "Commodo ea enim dolor enim adipisicing ut mollit .",日期":2016 年 7 月 15 日"注释": [{"username": "Tran","content": "Ea aliqua sat fugiat adiisicing."},{"username": "Noreen","content": "Commodo qui ea nulla est offcia. 非."},{"用户名": "吉列姆","content": "Esse cupidatat adipisicing 坐最小."}]}]

所以我的问题......是否只有一个请求可能?:) 谢谢

解决方案

你可以通过 LEFT JOIN 来获取你需要的所有数据，在迭代它时你只需要关心你如何处理哪些数据.

$lastArticleId = null;$comments = [];$元素 = [];$sql = 'SELECT Articles.*, Comments.content AS comment, Comments.username FROM Articles LEFT JOIN Comments ON Articles.id = Comments.id_article';$result = $pdo->query($sql);foreach ($result->fetchAll(PDO::FETCH_OBJ) 作为 $dataset) {if($lastArticleId !== $dataset->id){$lastArticleId = $dataset->id;$elements[$lastArticleId] = ['id' =>$dataset->id,'标题' =>$数据集->标题，'内容' =>$数据集->内容，'日期' =>$数据集->日期，'评论' =>[],];}$comments[$lastArticleId][] = ['内容' =>$dataset->评论，'用户名' =>$dataset->用户名，];}foreach ($elements as $key => $article) {$article['comments'] = $comments[$key];$fullData[] = $article;}回声 json_encode($fullData);

但是:仅仅因为你可以，并不意味着你应该.

将此任务拆分为两个查询会更容易编写和阅读.因此，如果您以后由于某种原因需要修改这段代码(或其他人)，他会很高兴您选择了 2 查询方法.

拆分任务也是一种很好的做法，因此理想情况下，您应该在数据库访问层 getAllArticles() 和另一个getCommentsByArticleId($id_article) 中创建方法.这样您就可以获取所有评论，而无需加载整篇文章.

Ok, I have a simple question... I have a database with two tables :

Articles (id, content, title, date)

Comments (id_article, username, content)

And I want to obtain a JSON array like this :

[
    {
    "id": "5785634a87c5a0075bf211de",
    "title": "Elita",
    "content": "Commodo ea enim dolor enim adipisicing ut mollit .",
    "date":"15July2016"
    "comments": [
        {
        "username": "Tran",
        "content": "Ea aliqua sit fugiat adipisicing."
        },
        {
        "username": "Noreen",
        "content": "Commodo qui ea nulla est officia. non."
        },
        {
        "username": "Gilliam",
        "content": "Esse cupidatat adipisicing sit minim."
        }
    ]
    }
]

So my question ... Is it possible with only one request? :) Thanks you

解决方案

You can LEFT JOIN to get all the data you need, when iterating over it you just have to take care about how you handle which data.

$lastArticleId = null;
$comments = [];
$elements = [];
$sql = 'SELECT Articles.*, Comments.content AS comment, Comments.username FROM Articles LEFT JOIN Comments ON Articles.id = Comments.id_article';
$result = $pdo->query($sql);
foreach ($result->fetchAll(PDO::FETCH_OBJ) as $dataset) {
    if($lastArticleId !== $dataset->id){
        $lastArticleId = $dataset->id;
        $elements[$lastArticleId] = [
            'id'       => $dataset->id,
            'title'    => $dataset->title,
            'content'  => $dataset->content,
            'date'     => $dataset->date,
            'comments' => [],
        ];
    }
    $comments[$lastArticleId][] = [
        'content' => $dataset->comment,
        'username' => $dataset->username,
    ];
}
foreach ($elements as $key => $article) {
    $article['comments'] = $comments[$key];
    $fullData[] = $article;
}
echo json_encode($fullData);

But: Just because you can, doesn't mean you should.

Splitting this task in two queries is a lot easier to write and to read. So if you later for some reason need to touch this piece of code (or someone else) he will be glad you chose the 2 query approach.

Also it is good practice to split tasks, so ideally you would create methods in your database-access-layer getAllArticles() and another one getCommentsByArticleId($id_article). This way you could fetch all the comments, without having to load the whole article.

这篇关于MySQL:请求使用 Array 创建一个 JSON 对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！