0

I have a json data from php which is working perfectly. What I want now is, I want my json data to be like the codes in the javascript function below. I want to iterate and get similar data like the objects in the javascript codes. How do I get it.

$json_array = array();

$sql = "SELECT id, instructions, quiz_question, correct, wrong, wrong1, wrong2 FROM student_quiz WHERE subject = 'SOCIAL STUDIES' AND type = 'challenge'";

$results = $pdo->query($sql);
$results->setFetchMode(PDO::FETCH_ASSOC);

while($row = $results->fetch()) {
    $json_array['quizlist'][] = $row ;
}

?>

    <script type='text/javascript'>



(function() {
  var questions = [{
    question: "What is 3*6?",
    choices: [3, 6, 9, 12, 18],
    correctAnswer: 4
  }, {
    question: "What is 8*9?",
    choices: [72, 99, 108, 134, 156],
    correctAnswer: 0
  }, {
    question: "What is 1*7?",
    choices: [4, 5, 6, 7, 8],
    correctAnswer: 3
  }, {
    question: "What is 8*8?",
    choices: [20, 30, 40, 50, 64],
    correctAnswer: 4
  }];
Robee
  • 23
  • 1
  • 5

2 Answers2

1

To output a JSON from database data it's good to do a mapping between the data if one of them can't be adjusted and customized more.

To output the mapped JSON array in PHP there is a function json_encode(). In the following example also shuffle() has been used to randomize the choices.

// ...
$sql = "SELECT id, instructions, quiz_question, correct, wrong, wrong1, wrong2 FROM student_quiz WHERE subject = 'SOCIAL STUDIES' AND type = 'challenge'";

$results = $pdo->query($sql);
$results->setFetchMode(PDO::FETCH_ASSOC);

$json = [];

while($row = $results->fetch()) {
    $choices = [
        $row['correct'],
        $row['wrong'],
        $row['wrong1'],
        $row['wrong2'],
    ];

    // shuffle the current choices so the 1st item is not always obviously correct
    shuffle($choices);

    $json[] = [
        'question' => $row['question'],
        'choices' => $choices,
        'correctAnswer' => $row['correct'],
    ];
}

echo json_encode($json);
Peter Kokot
  • 46
  • 1
  • 3
0

Well there are issues with your current - lets say - "concept".

At first lets care about the database:

You do store questions and answers of any kind at the same table as neighboor fields of the actual row. This might be easy to create and handle, but this is not for what databases are intended for. MySQL (or any other derivate or similar database) is intended to create relations to datasets of a table to another. This means that the entire performance of queries roots in relationships. You'll get in performance issues when you later run complex queries who actually join the same table or modify the contents of tables for a query scope.

It is recommended to normalize your actual database design in a way that provides the ability to assign answers to questions and states of answers to questions and answers in 2 additional individual tables (best practice from my point of view). Once you do this, you can utilize maintenance mechanisms of the database server towards those tables. You could set the maintenance actions for the question_id-fields of answers and answer states to "ON DELETE CASCADE" to ensure that when questions are deleted all corresponding datasets of other tables are deleted too.

To get the required knowledge, you may invest some time and read this almost complete SO-Entry: MySQL foreign key constraints, cascade delete

Once you get the required knowledge about the normalization of your database you can than decide how the constructed array must be defined to achieve the "combinition of objects and arrays in a JSON representation". Some facts you might keep in mind when creating the logic to compose the array is:

  • Objects in JSON are expressed when an associative array (in PHP) contains string (non-numeric) keys.
  • Arrays in JSON are expressed when an array (in PHP) contains integer keys of an consecutive order starting with 0.

In your concrete JS-Example, the source array might be based on a Array containing integer indexed arrays which have a string index. Once you understand all of this, you might get the right hint to construct the exact same array with your contents.

nhlm
  • 55
  • 5