php - 如何 php 查询并编码为 json 级别 2

Question

这是我的数据库表

Questions Tableid | qname                           | ansid1  | 1.what's your favorite color ?  |  a-012  | 2.what's your hair color ?      |  a-01

Answer Tableid | ansid | aname1  | a-01  | red2  | a-01  | blue3  | a-01  | green

when i use php query and encode to json

 select * from questions inner join answers on question.ansid = answer.ansid

 [
     {
       "question": "1. What's your favorite color ?",
       "choices": [{"a": "red"}]
     },
     {
       "question": "1. What's your favorite color ?",
       "choices": [{"a": "blue"}]
     },
     {
       "question": "1. What's your favorite color ?",
       "choices": [{"a": "green"}]
     },
     {
       "question": "2. what's your hair color ?",
       "choices": [{"a": "red"}]
     },
     {
       "question": "2. what's your hair color ?",
       "choices": [{"a": "blue"}]
     },
     {
       "question": "2. what's your hair color ?",
       "choices": [{"a": "green"}]
     }
    ]   

是否可以像这样编码json数据

[
 {
  "question": "1. What's your favorite color ?",
  "choices": [{"a": "red"},{"a": "blue"},{"a": "green"}]
 },
 {
  "question": "2. What's your hair color ?",
  "choices": [{"a": "red"},{"a": "blue"},{"a": "green"}]
 }
]

Answer 1

你可以在下面的两个查询中得到这个-

// first select questions with your criteria for me limiting to 20
 $stmt = $pdo->prepare("SELECT * FROM questions limit ?");
 $stmt = $stmt->execute([20]);
 $questions = $stmt->fetchAll(PDO::FETCH_ASSOC);
 //collect ansid 
 $ansIds = [];
 foreach($questions as $qst)
 {
     $ansIds[] = $qst['ansid'];
 }

 $ansIds = array_unique($ansIds);

// now collect answers belongs above questions

$stmt = $pdo->prepare("SELECT * FROM answers WHERE ansis in (?)");
$stmt = $stmt->execute([implode(",", $ansIds)]);
$answers = $stmt->fetchAll(PDO::FETCH_ASSOC);
// now combine them having loop like below
// pass by reference in foreach
 foreach($questions as &$question)
 {
     $question['choices'] = [];
     foreach($answers as $ans)
     {
        if($question['ansid'] == $ans["ansid"])
        {
          array_push($question['choices'], ['a' => $ans['aname']]);
        }
     }
 }
// now your $questions variable is ready 

 echo json_encode($questions);