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 .",
"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 Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download