MySQL：请求使用Array创建JSON对象

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.

At the end, you will need to do 2 queries, -.-'

Here would be the PHP code (getJSON.php, for example):

$array = [];
$sql = 'SELECT * FROM Articles';
$q = mysql_query($sql) or die ("Error in $sql: ".mysql_error());
while ($rs = mysql_fetch_object($q)) {
    $element = [
        'id'       => $rs->id,
        'title'    => $rs->title,
        'content'  => $rs->content,
        'date'     => $rs->date,
        'comments' => [],
    ];

    $sql2 = 'SELECT * FROM Comments WHERE id_article = '.$rs->id;
    $q2 = mysql_query($sql2) or die ("Error in $sql2: ".mysql_error());
    while ($rs2 = mysql_fetch_object($q2)) {
        $comment = [
            'username' => $rs2->username,
            'content' => $rs2->content,
        ];

        $element['comments'] = $comment;
    }

    $array[] = $element;
}

echo json_encode($array);

And this could be like the jQuery or Javascript call to obtain that JSON generated in the php script:

jQuery.getJSON('getJSON.php', {}, function (data) {}

And well, then the rest you already have it I guess...

I hope it helped you. Let me know! And good luck! ;)