php 使用 json_encode 从 MySQL 查询返回 JSON 对象

Return JSON object from MySQL query using json_encode

提问by Tiffany Lowe

The question was long, so I had to shorten it.

问题很长，所以我不得不缩短它。

Anyway, I currently have the following table with the following results.

无论如何，我目前有下表，结果如下。

What I am doing is the following:

我正在做的是以下内容：

1. Query all the answers associated to one question
2. Encoding it after storing it into an array

1. 查询与一个问题相关的所有答案
2. 将其存储到数组中后对其进行编码

This is my current query:

这是我当前的查询：

$stmt = "SELECT questions.question_text, answers.answer_text 
   FROM     questions, answers, test
   WHERE    questions.question_id = answers.question_id
   AND      questions.test_id =1";

$result = $connection->query($stmt);

Which gives me this:

这给了我这个：

This is the PHP:

这是PHP：

$encode = array();

while($row = mysqli_fetch_assoc($result)) {
   $encode[] = $row;
}

echo json_encode($encode);  

Which gives me this output:

这给了我这个输出：

[
    {
        "question_text": "What is HTML?",
        "answer_text": "HTML is a Hypertext Markup Language"
    },
    {
        "question_text": "What is HTML?",
        "answer_text": "HTML is a Hypertext Markup Language"
    },
    {
        "question_text": "What is HTML?",
        "answer_text": "HTML is a food"
    },
    {
        "question_text": "What is HTML?",
        "answer_text": "HTML is a food"
    },
    {
        "question_text": "What is HTML?",
        "answer_text": "HTML is an Asynchronous language"
    },
    {
        "question_text": "What is HTML?",
        "answer_text": "HTML is an Asynchronous language"
    },
    {
        "question_text": "What is HTML?",
        "answer_text": "HTML is a styling language"
    },
    {
        "question_text": "What is HTML?",
        "answer_text": "HTML is a styling language"
    }
]

This is the desiredoutput with json_encode:

这是json_encode所需的输出：

"What is HTML?": {
        "1": "HTML is a Hypertext Markup Language",
        "2": "HTML is a food",
        "3": "HTML is an Asynchronous language",
        "4": "HTML is a styling language"
    }

What I am currently getting is multiple singleobjects with one of the answers within them but always the answer associated to it. I wish to make a single object with all of the answers in it and the question representing the object. I really hope this makes sense. I am probably way off in my logic, so please forgive me for that.

我目前得到的是多个单个对象，其中包含一个答案，但始终是与之相关的答案。我希望制作一个包含所有答案和代表该对象的问题的对象。我真的希望这是有道理的。我的逻辑可能有点偏差，所以请原谅我。

I tried playing around with the while loop but I couldn't get it to work. Can someone lead me the right way towards achieving my desired output?

我尝试使用 while 循环，但我无法让它工作。有人可以引导我以正确的方式实现我想要的输出吗？

Thank you.

谢谢你。

采纳答案by Tiffany Lowe

I changed my query to the following:

我将查询更改为以下内容：

SELECT DISTINCT questions.question_text, answers.answer_text 
   FROM     questions, answers, test
   WHERE    questions.question_id = answers.question_id
   AND      questions.test_id =

The while loop to this:

这个while循环：

while($row = mysqli_fetch_assoc($result)) {
    $encode[$row['question_text']][] = $row['answer_text'];
}

This gave me this:

这给了我这个：

{
    "What is HTML?": [
        "HTML is a Hypertext Markup Language",
        "HTML is a food",
        "HTML is an Asynchronous language",
        "HTML is a styling language"
    ]
}

Which I can now work with.

我现在可以使用它。

回答by ficuscr

Sounds like just altering the array you are building out...

听起来只是改变你正在构建的数组......

$encode = array();

while($row = mysqli_fetch_assoc($result)) {
   $encode[$row['question _text']][] = $row['answer_text'];
}

echo json_encode($encode);

回答by hugo jan

Change this bit:

改变这一点：

$encode = array();

while($row = mysqli_fetch_assoc($result)) {
   $encode[] = $row;
}

To (start at 1 I have added the $i, instead of just pushing it to the end of the encode array):

到（从 1 开始，我添加了 $i，而不是将它推到编码数组的末尾）：

$encode = array();
$i = 1;
while($row = mysqli_fetch_assoc($result)) {
   $encode[$row['question_text']][$i] = $row['answer_text'];
   $i++;
}

And you should be alright.

你应该没事的。

回答by Leo

You do not need to use PHP to do extra processing.

您不需要使用 PHP 进行额外的处理。

Just use mysql Group By

只需使用 mysql Group By

$stmt = "SELECT questions.question_text, answers.answer_text 
  FROM     questions, answers, test
  WHERE    questions.question_id = answers.question_id
  AND      questions.test_id =1
  GROUP BY questions.question_id;"