-1

I try to get a json form two different table in php/mysql. Purpose is to print JSON out for a rest API using php/mysql.

Expected json is:

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

What I had try with 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);
}

Current Output using 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: Questions-

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: Options -

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;

Seems its returns only question no options also pushed title twice on each questions. Please any one help me to resolve this.

  • First, you need to call array_push($q['qoptions'], $options)(you are omitting the "q" in front of "options". What is your current output? – Johannes Apr 11 at 13:23
  • The expected output does not look well structure. Why do you nest an array within that qoption option which you haven't closed yet? – Nico Haase Apr 11 at 13:43
  • Purpose is to creating a rest api for getting questions with there options (mcq) for each questions. – Daniel Smith Apr 11 at 13:47
  • And that's why you provide a very long string as the third element of that one element you return to the requesting side? – Nico Haase Apr 11 at 13:50
  • @NicoHaase I think it is a typo, the " is missing after "qoption". – Johannes Apr 11 at 13:51
0

You are adding the options to the $q variable that is only visible inside the second foreach loop. Use your $questions array instead. Furthermore, you need to get the current index of your question to add by adding $key => $q in your foreach head. Can you tell me what the output of this code is (I changed everything mentioned before)?

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);
}
  • I edited my answer (last line of second foreach): $questions[$key]['options'] = $options; you should at least get an empty array. I don't know your database structure exactly. – Johannes Apr 11 at 13:50
  • Okey seems looks good to me but was not sure what 1,2,3,4 add before my expected key/value – Daniel Smith Apr 11 at 14:05
  • By using specific attribute names in your SQL statements instead of just * you could limit the fields you get in your array. In addition to that, you can construct a new array or object for every question and object having only the fields you want and then assign the corresponding database fields. Maybe you can share an export of your database structure in order to see what is returned by the SQL statements. – Johannes Apr 11 at 14:09
  • Updated questions with both table structure. ty – Daniel Smith Apr 11 at 14:23
  • Also updated query with specific fields but still getting1,2,3 etc. check url again. – Daniel Smith Apr 11 at 14:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.