时间戳

首页 > 解决方案 > PHP - 如何使用 php 和 mysql 在 for 循环中更新数组对象

php - PHP - 如何使用 php 和 mysql 在 for 循环中更新数组对象

问题描述

我尝试在 php/mysql 中从两个不同的表中获取一个 json。目的是使用 php/mysql 为 rest API 打印 JSON。

预期的json是：

{
   "qid":"1",
   "qst":"OK",
   "qoption:
     [
      {"id":"o1","isrt":true},
      {"id":"o2","isrt":false},
      {"id":"o3","isrt":false},
      {"id":"o4","isrt":false}
     ]
},
{
   "qid":"2",
   "qst":"OK",
   "qoption:
     [
      {"id":"o1","isrt":flase},
      {"id":"o2","isrt":false},
      {"id":"o3","isrt":true},
      {"id":"o4","isrt":false}
     ]
}

我对 PHP 的尝试：

if ( isset($_GET['examsid']) && $_GET['examsid'] != "") {

    $questions = array();
    $conn = dbConnection();
    $examsid = $_GET['examsid'];

    $result = mysqli_query($conn, "SELECT `id`,`exams_id`,`title` FROM `tblquestions` WHERE exams_id='".$examsid."' ORDER BY RAND()");

    if(mysqli_num_rows($result) > 0){
       while($q = mysqli_fetch_array($result)){
           $questions[] = $q;
       }
    }

    foreach ($questions as $q) {
       $options = array();
       $oquery = mysqli_query($conn, "SELECT `id`,`question_id`,`title`,`iscorrect` FROM `tbloptions` WHERE `question_id`='".$q['id']."' ORDER BY RAND()");
       while($o = mysqli_fetch_array($oquery)){
         $options[] = $o;
       }
       array_push($q['options'], $options);
    }


    print_r($questions);
}

使用 json_encode($jsonobj) 的当前输出： 

    {
      "0": "1",
      "1": "1",
      "2": "How many bones comprise the adult human skeleton?",
      "3": "2019-04-11 11:18:44",
      "4": "0000-00-00 00:00:00",
      "id": "1",
      "exams_id": "1",
      "title": "How many bones comprise the adult human skeleton?",
      "c_date": "2019-04-11 11:18:44",
      "m_date": "0000-00-00 00:00:00"
    },
    {
      "0": "3",
      "1": "1",
      "2": "Which of the following is the first calculating device?",
      "3": "2019-04-11 11:19:56",
      "4": "0000-00-00 00:00:00",
      "id": "3",
      "exams_id": "1",
      "title": "Which of the following is the first calculating device?",
      "c_date": "2019-04-11 11:19:56",
      "m_date": "0000-00-00 00:00:00"
    }

MySQL：问题-

CREATE TABLE `tblquestions` (
  `id` int(11) NOT NULL,
  `exams_id` varchar(100) CHARACTER SET latin1 NOT NULL,
  `title` varchar(500) CHARACTER SET latin1 NOT NULL,
  `c_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `m_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

MySQL：选项 -

CREATE TABLE `tbloptions` (
  `id` int(11) NOT NULL,
  `question_id` int(100) NOT NULL,
  `title` varchar(300) NOT NULL,
  `iscorrect` varchar(100) CHARACTER SET latin1 NOT NULL,
  `c_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `m_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

似乎它只返回一个问题，没有选项也在每个问题上两次推送标题。请任何人帮我解决这个问题。

标签： phpmysql

解决方案

您正在将选项添加到$q仅在第二个 foreach 循环中可见的变量。请改用您的$questions阵列。$key => $q此外，您需要通过添加foreach 头来获取要添加的问题的当前索引。你能告诉我这段代码的输出是什么（我改变了前面提到的一切）吗？

if ( isset($_GET['examsid']) && $_GET['examsid'] != "") {

    $questions = array();
    $conn = dbConnection();
    $examsid = $_GET['examsid'];

    $result = mysqli_query($conn, "SELECT * FROM `tblquestions` WHERE exams_id='".$examsid."' ORDER BY RAND()");

    if(mysqli_num_rows($result) > 0){
       while($q = mysqli_fetch_array($result)){
           $questions[] = $q;
       }
    }

    foreach ($questions as $key => $q) {
       $options = array();
       $oquery = mysqli_query($conn, "SELECT * FROM `tbloptions` WHERE `question_id`='".$q['id']."' ORDER BY RAND()");
       while($o = mysqli_fetch_array($oquery)){
         $options[] = $o;
       }
       $questions[$key]['options'] = $options;
    }


    print_r($questions);
}

推荐阅读