使用json_encode从MySQL查询返回JSON对象 [英] Return JSON object from MySQL query using json_encode

问题描述

这个问题很长，所以我不得不缩短它.

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. 查询与一个问题相关的所有答案
2. 将其存储到数组中后对其进行编码

这是我当前的查询:

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

哪个给我这个:

这是PHP:

$encode = array();

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

echo json_encode($encode);  

哪个给我这个输出:

[
    {
        "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"
    }
]

这是带有json_encode的期望的输出:

This is the desired output with 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 single objects 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.

我尝试使用while循环，但是无法正常工作.有人可以引导我以正确的方式实现期望的输出吗?

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?

谢谢.

推荐答案

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

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 =

while循环:

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

这给了我这个

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

我现在可以与之合作.

这篇关于使用json_encode从MySQL查询返回JSON对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！