Return JSON object from MySQL query using json_encode

Question

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:

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

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:

enter image description here

This is the 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 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.

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?

Thank you.

your desired output is neither a feature from json_encode nor mysql related functions. You have to reorganize your objects in $encode yourself. — charlee
– charlee, Commented Feb 28, 2014 at 20:11
Are there multiple questions for which you want the json encoded outputs or do you want it only for 1 question ? — Maximus2012
– Maximus2012, Commented Feb 28, 2014 at 20:14
I think a nested database iteration. I think it would be a very logical thing to do. Based on the question ID, get all of the answers associated to it. That sounds pretty right, but I wouldn't know how to encode it. — ChainFuse
– ChainFuse, Commented Feb 28, 2014 at 20:23

ficuscr · Accepted Answer · 2014-02-28 20:14:51Z

9

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

edited Feb 28, 2014 at 20:14

answered Feb 28, 2014 at 20:11

ficuscr

7,0932 gold badges35 silver badges54 bronze badges

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

7 Comments

charlee Over a year ago

Not exactly what OP wants but this is good enough to provide a clear approach.

ficuscr Over a year ago

tbh question was so long I skimmed :)

ChainFuse Over a year ago

This gives me an error. Doesn't output anything. I believe it should. I even removed the space from "question _text" and no luck.

Maximus2012 Over a year ago

@TiffanyLowe what is the error message that you are getting ?

ChainFuse Over a year ago

This question definitely helped me do what I wanted WITH a little tweaking from my side.

|

hugo jan · Accepted Answer · 2014-02-28 20:13:53Z

0

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):

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

And you should be alright.

answered Feb 28, 2014 at 20:13

hugo jan

1765 bronze badges

Comments

Leo · Accepted Answer · 2014-02-28 20:22:49Z

0

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

Just use 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;"

answered Feb 28, 2014 at 20:22

Leo

3452 silver badges11 bronze badges

2 Comments

ChainFuse Over a year ago

This returns only one question.

Leo Over a year ago

Could you give me a brief explanation of your table structure

ChainFuse · Accepted Answer · 2014-02-28 20:39:13Z

0

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($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.

answered Feb 28, 2014 at 20:39

ChainFuse

8272 gold badges11 silver badges26 bronze badges

Collectives™ on Stack Overflow

Return JSON object from MySQL query using json_encode

4 Answers 4

7 Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related