个人中心
gpt4 book ai didi

php - 使用 mySQL 和 php 创建正确的 JSON

转载 作者：行者123 更新时间：2023-11-30 01:29:00 25 4
gpt4 key购买 nike

我很确定我正在将多个任务混合在一起，但是请耐心等待，我是 mySQL 和 php 的新手。

我正在使用 json_encode 从 mySQL 查询创建 JSON。但在回应之前，我想修改 JSON，以添加更多有值(value)的数据。目前我的 JSON 如下所示:

[
  {
    "question": "1",
    "questionText": "question text 1",
    "categoryID": "1",
    "answerID": "1",
    "answerText": "answer text 1",
    "isTrue": "1"
  },
  {
    "question": "1",
    "questionText": "question text 1",
    "categoryID": "1",
    "answerID": "2",
    "answerText": "answer text 2",
    "isTrue": "0"
  },
  {
    "question": "1",
    "questionText": "question text 1",
    "categoryID": "1",
    "answerID": "3",
    "answerText": "answer text 3",
    "isTrue": "0"
  },
  {
    "question": "1",
    "questionText": "question text 1",
    "categoryID": "1",
    "answerID": "4",
    "answerText": "answer text 4",
    "isTrue": "0"
  },
  {
    "question": "2",
    "questionText": "question text 2",
    "categoryID": "2",
    "answerID": "1",
    "answerText": "answer text 1",
    "isTrue": "0"
  },
  {
    "question": "2",
    "questionText": "question text 2",
    "categoryID": "2",
    "answerID": "2",
    "answerText": "answer text 2",
    "isTrue": "1"
  },
  {
    "question": "2",
    "questionText": "question text 2",
    "categoryID": "2",
    "answerID": "3",
    "answerText": "answer text 3",
    "isTrue": "0"
  },
  {
    "question": "2",
    "questionText": "question text 2",
    "categoryID": "2",
    "answerID": "4",
    "answerText": "answer text 4",
    "isTrue": "0"
  }
]

我希望它看起来像这样(伪代码):

    exam:{
    questions:[
        question:{
            questionID: string
            questionTest: string
            categoryID: string
            correctAnswerID: string
            chosenAnswerID: string
            answers:[
                answer:{
                    answerID = string
                    answerText = string
                    isTrue = bool
                }
                answer:{}
            ]
        }
        question:{}
     ]
     categoryID: string
 }

这是到目前为止的代码:

<html>
<body>

<?php
    header('Content-Type: text/html; charset=utf-8');
    $con=mysqli_connect("localhost","root","root","Theory");
    // Check connection
    if (mysqli_connect_errno())
    {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }
    $result = mysqli_query($con,"SELECT `questions`.`questionID` AS questionID,
                           `questions`.`questionText` AS questionText,
                           `questions`.`categoryID` AS categoryID,
                           `answers`.`answerID` AS answerID,
                           `answers`.`answerText` AS answerText,
                           `answers`.`isTrue` AS isTrue
                                 FROM `questions`,`answers`
                                 WHERE `questions`.`questionID` = `answers`.`questionID`");

    if (!$result)
    {
        die('Error: ' . mysqli_error($con));
    }

   $rows = array();
   while($r = mysqli_fetch_assoc($result)) {
        $rows[] = $r;
   }
   print json_encode($rows);

    mysqli_close($con);
    ?>

</body>
</head>

最佳答案

我个人的偏好和为了便于使用，我会避免将问题和答案表连接在一起。特别是因为无论如何你都需要在 json 中将它们分开。这是一个带有一些注释的重写，可以帮助您理解正在发生的事情。 注意:代码尚未使用数据库进行测试，因此它可能无法完全正常工作，因为我不知道您的表的结构，但它应该可以帮助您入门。

<?php 

// header information needs to be set before any output or it will error.
header('Content-Type: text/html; charset=utf-8');

?><html>
    <body>

<?php

$con=mysqli_connect("localhost","root","root","Theory");
// Check connection
if (mysqli_connect_errno())
{
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$questions = array();

// Lets get a list of the questions
$questions_results = mysql_query("SELECT `questions`.`questionID`, `questions`.`questionText`, `questions`.`categoryID` FROM `questions`") or die('Error: ' . mysqli_error($con));
while($question = mysqli_fetch_object($questions_results))
{

    // Now that we have a $question object lets add an answers property and set it to a blank array
    $questions[$question->questionID]->answers = array();

    // Now query for answers table to build on the answers array
    $answers_results = mysql_query("SELECT `answers`.`answerID`, `answers`.`answerText`, `answers`.`isTrue` FROM `answers` where `answers`.`questionID` = '".$question->questionID."'") or die('Error: ' . mysqli_error($con));
    while($answer = mysqli_fetch_object($questions_results))
    {
        $questions[$question->questionID]->answers[] = $answer;
    }

    // Now lets add this nifty new question to the $questions array
    $questions[] = $question;
}
mysqli_close($con);


// Now we can prin the json version of $questions
json_encode($questions);

?>

    </body>
</head>

上面的代码的作用是构建一个数组/对象，如果手动构建，将像这样构建: 

        // Question 1 - Notice the 0 key index on the q
        $questions[0] = new stdClass(); // Create a new object
        $questions[0]->questionID = 1;
        $questions[0]->questionText = "Question";
        $questions[0]->categoryID = 1;
        $questions[0]->answers = array(); // Create a new array for answers

            // Answer 1 for question 1 - notice the 0 key index on the answer
            $questions[0]->answers[0] = new stdClass(); // Create a new object for answer 1
            $questions[0]->answers[0]->answerID = 1;
            $questions[0]->answers[0]->answerText = "Answer";
            $questions[0]->answers[0]->isTrue = 1;

            // Answer 2 for question 1 - notice the 1 key index on the answer
            $questions[0]->answers[1] = new stdClass(); // Create a new object for answer 1
            $questions[0]->answers[1]->answerID = 1;
            $questions[0]->answers[1]->answerText = "Answer";
            $questions[0]->answers[1]->isTrue = 1;


        // Question 2 - notice the 1 key index on the questions
        $questions[1] = new stdClass(); // Create a new object
        $questions[1]->questionID = 1;
        $questions[1]->questionText = "Question";
        $questions[1]->categoryID = 1;
        $questions[1]->answers = array(); // Create a new array for answers

            // Answer 1 for question 2
            $questions[1]->answers[0] = new stdClass(); // Create a new object for answer 1
            $questions[1]->answers[0]->answerID = 1;
            $questions[1]->answers[0]->answerText = "Answer";
            $questions[1]->answers[0]->isTrue = 1;
?>

关于php - 使用 mySQL 和 php 创建正确的 JSON，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/17681414/

25 4 0
文章推荐： php - 使用 php 优化多周 mysql 查询
文章推荐： MySQL 查询运行缓慢
文章推荐： mysql - DELETE FROM with LIMIT 不起作用 - 错误 1064
文章推荐： c++ - 读取输入文件时不打印空格
行者123
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com