Two MySQL queries in JSON objects

Question

I am trying to build the following JSON through MySQL and PHP.
Each Chapter has an id, a chapter title, and a list of topics.
Each topic has an id and a topic area

{
"Chapters": [
    {
        "id": "1",
        "chapterTitle": "Introduction",
        "Topics": [
            {
                "id": "1",
                "topicArea": "C++"
            },
            {
                "id": "2",
                "topicArea": "Java"
            }
        ]
    },
    {
        "id": "2",
        "chapterTitle": "Getting Started",
        "Topics": [
            {
                "id": "1",
                "topicArea": "Start Here"
            }
        ]
    }
 ]
}

However, I am not able to generate this output if I try the following PHP code (1)

//Get all chapters
$result = mysql_query("SELECT * FROM chapters");      

while ($row = mysql_fetch_array($result))
{
    $json[] = $row;
    $json2 = array();
    $chapterid = $row["id"];

    //Fetch all topics within the first book chapter
    $fetch = mysql_query("SELECT id,topicArea FROM topics where chapterid=$chapterid");
    while ($row2 = mysql_fetch_assoc($fetch))
    {
         $json2[] = array( 
            'id' => $row2["id"],
            'topicArea' => $row2["topicArea"]
           );
     }
     $json['Topics'] = $json2;          //I think the problem is here!
}
echo json_encode($json);

You're telling us that something is wrong, but you're not telling us what you get as a result instead. We need to see that in order to do more than point out logical flaws. — DiMono
– DiMono, Commented Sep 10, 2013 at 4:06

Community · Accepted Answer · 2017-05-23 12:05:11Z

1

Please don't use the mysql_* functions any more. Both mysqli and PDO enables you to use prepared statements.

With that said, you are right that $json['Topics'] = $json2; is the problem: this needs to be $json['Topics'][] = $json2;.

Finally for performance reasons (look at What is SELECT N+1?), you might want to look into a JOIN. All in all:

$dbh = new PDO("mysql:host=$db_host;dbname=$db_name", $db_user, $db_pass);
$query = 'SELECT c.id AS chapter_id, c.chapterTitle, t.id as topic_id, t.topicArea FROM chapters c INNER JOIN topics t ON c.id = t.chapterid';
$json = array();
foreach ($dbh->query($query) as $row) {
  $json[$row['chapter_id']]['id'] = $row->chapter_id;
  $json[$row['chapter_id']]['chapter_title'] = $row->chapter_title;
  $json[$row['chapter_id']]['Topics'][] = array(
    'id' => $row->topic_id,
    'topicArea' => $row->topicArea,
  );
}
print json_encode(array_values($json));

edited May 23, 2017 at 12:05

CommunityBot

11 silver badge

answered Sep 10, 2013 at 3:59

chx

11.9k8 gold badges64 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Two MySQL queries in JSON objects

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related