SQL Question

MySQL : Request to make an JSON object with Array

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

Answer

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

Comments