add an array to json object with PhP

Question

I want to encode an array of answers to a question when retrieving that question from the database.

Desired Result:

[{"question":"What Barks","answers":["Cats","Dogs","Birds","Elephants"],"correct":1}]

Source Code

require_once("connect.php");
$sql = "SELECT questions.question, GROUP_CONCAT(answers.answer) answers, questions.correct FROM questions,answers  where answers.questionID = questions.questionID group by questions.question";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    $data = array();
    while($row = mysqli_fetch_assoc($result)) {
        $data[] = $row;
    }
}

echo json_encode($data);

mysqli_close($conn);

Current Result:

[{"question":"What Barks?","answers":"Cat,Dog,Fish,Cow","correct":"1"}]

Try to chnage From $sql = "SELECT questions.question, questions.correct, answers.answer FROM questions INNER JOIN answers ON answers.questionID = questions.questionID"; TO $sql = "SELECT questions.question, questions.correct, answers.answer As answersFROM questions INNER JOIN answers ON answers.questionID = questions.questionID"; — Nirali
– Nirali, Commented Apr 12, 2018 at 6:52
You will need to format the array to suit your desired result. Right now you are just encoding the DB result. — Jite
– Jite, Commented Apr 12, 2018 at 6:52
I think your DB schema needs some work! Your data isn't even 1NF — GordonM
– GordonM, Commented Apr 12, 2018 at 9:31

Zhorov · Accepted Answer · 2018-04-12 09:29:16Z

1

Here is sample solution, based on example in your question:

require_once("connect.php");
$sql = 
    "SELECT questions.question, GROUP_CONCAT(answers.answer) answers, questions.correct FROM questions, answers WHERE answers.questionID = questions.questionID GROUP BY questions.question";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    $data = array();
    while($row = mysqli_fetch_assoc($result)) {
        $a = array(
            "question"=>$row["question"],
            "answers"=>explode(",", $row["answers"]),
            "correct"=>$row["correct"]
        );
        $data[] = $a;
    }
}
echo json_encode($data);
mysqli_close($conn);

answered Apr 12, 2018 at 9:29

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

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

Comments

make-me-alive · Accepted Answer · 2018-04-12 07:43:43Z

1

Change the mysql query as follows:

> SELECT questions.question, 
> questions.correct,GROUP_CONCAT(answers.answer) answers FROM
> questions,answers  where answers.questionID = questions.questionID
> group by questions.question

Here we use a GROUP_CONCAT aggregate function from MySQL

.

answered Apr 12, 2018 at 7:43

make-me-alive

1112 silver badges3 bronze badges

2 Comments

Bradley Over a year ago

Thanks for reply, almost works as intended currently the result is producing [{"question":"What Barks?","answers":"Cat,Dog,Fish,Cow","correct":"1"}] How do I add it in to an array

make-me-alive Over a year ago

Can you try this @Bradley

if (mysqli_num_rows($result) > 0) {     $data = array();     while($row = mysqli_fetch_assoc($result)) { 		$answers = $row["answers"]; 		$row["answers"] = explode(',', $answers);         $data[] = $row; 		     } }

Collectives™ on Stack Overflow

add an array to json object with PhP

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related