CODEWITHSUNDEEP
phpjson
Bradley
Bradley

Reputation: 98

add an array to json object with PhP

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

Upvotes: 0

Views: 81

Answers (2)

Zhorov
Zhorov

Reputation: 29943

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

Upvotes: 1

make-me-alive
make-me-alive

Reputation: 111

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

.

Upvotes: 1

Related Questions