Insert the results of multiple tables into one json_encode ()

Question

Sorry my language mistakes, I speak French.

I did some research on this site and elsewhere, but what I found did not solve my problem.

I make a website where people can publish their stories. I am working on a page where the author should be able to see his stories and the chapters of each of them.

I query two tables to get headings for stories and chapters. I have a “story” table that has a title and a summary of each story (and other things like id and publication date). And another table called “chapters”, in which there is a title for each chapter and an identifier for the story to which they belong.

I would like to get these elements later in my html to have something like this:

---

Story Title 1

Chapter 1

Chapter 2

---

So, I am doing json_encode (), but JSONLint said that the json that I get is invalid. PHP is new to me, I started to learn, so I'm probably writing my code incorrectly.

Here is my code:

<?php session_start(); require_once('connexion_db.php'); if($db=connect()){ $userID = $_SESSION['id']; $arr = array(); $reqRecits = $db->query('SELECT titre, id FROM recits WHERE user_id = '.$userID.''); $resRecits = $reqRecits->fetchAll(PDO::FETCH_ASSOC); foreach ($resRecits as $r){ $recitID = $r['id']; $recitTitre = $r['titre']; $reqChapitres = $db->query('SELECT id, titre_chapitre FROM chapitres WHERE recit_id = '.$r['id'].''); $resChapitres = $reqChapitres->fetchAll(PDO::FETCH_ASSOC); foreach ($resChapitres as $c){ $chapTitre = $c['titre_chapitre']; } $arr = array('titre'=>$recitTitre,'chapitre'=>$chapTitre); echo json_encode($arr, JSON_UNESCAPED_UNICODE); } }else{ echo "bdd non connectée."; } ?> 

I tested the proposed method here , but the result is worse. I do not know what to do: (

Thank you for your help!

Answer 1

It looks like you want the inner loop to look like this:

 $chapTitre = array(); foreach ($resChapitres as $c){ $chapTitre[] = $c['titre_chapitre']; } 

This way your JSON will include all the chapters.

Also, as you can see, you are listing a series of unrelated JSON objects, which should probably be the only array that will be a legitimate JSON object.

 foreach ($resRecits as $r){ $recitID = $r['id']; $recitTitre = $r['titre']; $reqChapitres = $db->query('SELECT id, titre_chapitre FROM chapitres WHERE recit_id = '.$r['id'].''); $resChapitres = $reqChapitres->fetchAll(PDO::FETCH_ASSOC); $chapTitre = array(); foreach ($resChapitres as $c){ $chapTitre[] = $c['titre_chapitre']; } $arr[] = array('titre'=>$recitTitre,'chapitre'=>$chapTitre); } echo json_encode($arr, JSON_UNESCAPED_UNICODE); 

Collects them all and displays them as a list.

 [ { 'titre': 'Title 1', 'chaptitre': ['Chapter 1', 'Chapter 2'] }, { 'titre': 'Title 2', 'chaptitre': ['Chapter 1', 'Chapter 2', 'Chapter 3'] }, ] 

Answer 2

 <?php session_start(); require_once('connexion_db.php'); if($db=connect()){ $userID = $_SESSION['id']; $arr = array(); $reqRecits = $db->query('SELECT titre, id FROM recits WHERE user_id = '.$userID.''); $resRecits = $reqRecits->fetchAll(PDO::FETCH_ASSOC); $chapTitres = array(); foreach ($resRecits as $r){ $recitID = $r['id']; $recitTitre = $r['titre']; $reqChapitres = $db->query('SELECT id, titre_chapitre FROM chapitres WHERE recit_id = '.$r['id'].''); $resChapitres = $reqChapitres->fetchAll(PDO::FETCH_ASSOC); foreach ($resChapitres as $c){ $chapTitres[] = $c['titre_chapitre']; } $arr[] = array('titre' => $recitTitre, 'chaptitres' => $chapTitres); } echo json_encode($arr, JSON_UNESCAPED_UNICODE); }else{ echo "bdd non connectée."; } ?>