0

I started learning json and developing a quiz app which fetches json from a php page to display it as html. I am selecting data from mysql table with php and printing it as json.

<?php
    header('Access-Control-Allow-Origin: *');
    header("Cache-Control: no-cache");
    header('Content-Type: application/json');
    include("dbcon.php");
    $sql = "SELECT * FROM quiz;";
    $result = $conn->query($sql);
    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
            echo json_encode($row, JSON_PRETTY_PRINT)."\r\n";
        }
    } 
    else {
        echo "nodata";
    }
$conn->close();
?>

the result is:

{
"id": "1",
"question": "ques1?",
"opt1": "Microsoft",
"opt2": "W3C",
"opt3": "Google",
"opt4": "IBM",
"answer": "W3C"
}
{
"id": "2",
"question": "ques2?",
"opt1": "Yahoo",
"opt2": "Google",
"opt3": "Bing",
"opt4": "DuckDuckGo",
"answer": "Google"
}

this code works perfectly fine and pretty prints json. But how to print the same key value as an array so the output is like-

{
    "id":[ "1", "2" ],
    "question":[ "ques1?", "ques2?" ],
    "opt1":[ "asd", "fgh" ],
    "opt2":[ "qwe", "rty" ],
    "opt3":[ "qwer", "vbbn" ],
    "opt4":[ "asdfg", "ascvb" ],
    "answer":[ "asd", "fgh" ],
}

I believe the result i expect will make it simple to read data from it using javascript by something like this:-

var q1 = myObj.question[0];

I saw many questions with the same title but they are all different cases. please provide a solution and correct my stupidity if I am wrong.

Improve this question
8
  • 2
    What you want to do is build an array $questions, then use $questions[] = $row;. With that all done, you can finish with json_encode($questions) to get your single array of question objects. Commented Jul 19, 2017 at 14:01
  • 1
    That is not valid JSON. And what you want to achieve is suboptimal at best. You want something like [{"id": ..., "question": ..., ...}, {"id": ..., ...}]. Commented Jul 19, 2017 at 14:01
  • 2
    "this code works perfectly fine and pretty prints json" - oh no it doesn't - that's not JSON. Commented Jul 19, 2017 at 14:02
  • @NiettheDarkAbsol Not only questions but but i want an array for all the keys. Json validators show this as valid json btw the comma in my expected result was a typo Commented Jul 19, 2017 at 14:08
  • 1
    json_encode() generates valid JSON but concatenating two valid JSON strings (as your current code does) doesn't make a valid JSON. JSON is just a string representation of some data structure. You have to build the data structure you need and encode it as JSON only once, at the end. Commented Jul 19, 2017 at 14:31

3 Answers 3

Reset to default
0

To get valid JSON with what you currently have you need to do this:

header('Access-Control-Allow-Origin: *');
header("Cache-Control: no-cache");
header('Content-Type: application/json');
include("dbcon.php");
$sql = "SELECT * FROM quiz;";
$result = $conn->query($sql);
$theResultIWant = [];
if ($result->num_rows > 0) {        
    while($row = $result->fetch_assoc()) {
        $theResultIWant[] = $row;
    }
} 
else {
    echo "nodata";
}
$conn->close();
echo json_encode($theResultIWant, JSON_PRETTY_PRINT);
//That is all

To get the result you want to get:

header('Access-Control-Allow-Origin: *');
header("Cache-Control: no-cache");
header('Content-Type: application/json');
include("dbcon.php");
$sql = "SELECT * FROM quiz;";
$result = $conn->query($sql);
$theResultIWant = [];
if ($result->num_rows > 0) {        
    while($row = $result->fetch_assoc()) {
        foreach ($row as $key=>$value) {
             $theResultIWant[$key] = $theResultIWant[$key]??[]; //or isset($theResultIWant[$key])?$theResultIWant[$key]:[]; in PHP < 7
             $theResultIWant[$key][] = $value;
        } 
    }
} 
else {
    echo "nodata";
}
$conn->close();
echo json_encode($theResultIWant, JSON_PRETTY_PRINT);

Either approach is reasonable to read JavaScript with, but the second one is said to be more compact in terms of size (however the first one can be compressed more if you're GZipping the result).

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

6 Comments

The line 12 is giving error. Was i supposed to change the code and not copy paste it like a dummy? Cannot use [] for reading in /home/hackupv7/public_html/hacksercise/data/quiz.php on line 12 I am missing something obvious.
@TusharTyagi which one is line 12? Also what version of PHP are you using?
ok using only $theResultIWant[$key][] = $value; produce the desired result thanks
@TusharTyagi the comment I put there was to indicate an alternative syntax if your PHP version was less than 7 (which I'm now guessing it is)
btw, the one with the comment is line 12, php version 5.5
|
0

Define an array for object before the while loop for example

$json=array();

With in the while loop do as following 

$json['object-key'][]=$row['key'];

example 

$json['id'][]=$row['id'];

After the while loop convert $json array to json object with json_encode(); function

Improve this answer

Comments

0 
        <?php
        header('Access-Control-Allow-Origin: *');
        header("Cache-Control: no-cache");
        header('Content-Type: application/json');
        include("dbcon.php");
        $sql = "SELECT * FROM quiz;";
        $result = $conn->query($sql);
        $dataArr = [];
        if ($result->num_rows > 0) {
            // output data of each row
            while($row = $result->fetch_assoc()) {                

                   $dataArr['id'][] = $row['id'];

                   $dataArr['question'][] = $row['question'];

                   $dataArr['opt1'][] = $row['opt1'];

                   $dataArr['opt2'][] = $row['opt2'];

                   $dataArr['opt3'][] = $row['opt3'];

                   $dataArr['opt4'][] = $row['opt4'];

                   $dataArr['answer'][] = $row['answer'];

            }
        } 
        else {
            echo "nodata";
        }
    $conn->close();
    ?>
echo json_encode($dataArr, JSON_PRETTY_PRINT);

You can use as I have coded above.

Improve this answer

1 Comment

using $dataArr['id'][]=$row['id']; is much shorter and simpler way and will work without using if statement.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.