current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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

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.

share|improve this question
    
do your questions have unique IDs ? –  Maximus2012 Feb 28 at 20:10
3  
your desired output is neither a feature from json_encode nor mysql related functions. You have to reorganize your objects in $encode yourself. –  charlee Feb 28 at 20:11
    
Yes they do. @Maximus2012 –  Tiffany Lowe Feb 28 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 Feb 28 at 20:14
1  
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. –  Tiffany Lowe Feb 28 at 20:23

4 Answers 4

up vote 3 down vote accepted

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);
share|improve this answer
2  
Not exactly what OP wants but this is good enough to provide a clear approach. –  charlee Feb 28 at 20:12
    
tbh question was so long I skimmed :) –  ficuscr Feb 28 at 20:13
    
This gives me an error. Doesn't output anything. I believe it should. I even removed the space from "question _text" and no luck. –  Tiffany Lowe Feb 28 at 20:21
    
@TiffanyLowe what is the error message that you are getting ? –  Maximus2012 Feb 28 at 20:22
    
Yeah, error message? Suppose make sure $row is what I think it is in that loop... Do you get the gist of what I am saying? Also make sure you used most recent, someone edited my answer a bit earlier and messed it up. –  ficuscr Feb 28 at 20:23
up vote 0 down vote

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.

share|improve this answer
up vote 0 down vote

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;"
share|improve this answer
    
This returns only one question. –  Tiffany Lowe Feb 28 at 20:23
    
Could you give me a brief explanation of your table structure –  Leo Feb 28 at 22:10
up vote 0 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.