[FIXED] How to add a select query in another select query in Codeigniter

Issue

I have two tables such as below :

/* questions table */
|  q_id   |  q_title               |
| ------- | ---------------------- |
|  1      |  What is your name?    |
| ------- | ---------------------- |
|  2      |  What is your gender?  |
| ------- | ---------------------- |
|  ...    |                        |

/* options table */
|  o_id  |  o_title  |  o_question_id  |
| ------ | --------- | --------------- |
|  1     |  George   |  1              |
| ------ | --------- | --------------- |
|  2     |  Sarah    |  1              |
| ------ | --------- | --------------- |
|  3     |  Michael  |  1              |
| ------ | --------- | --------------- |
|  4     |  Male     |  2              |
| ------ | --------- | --------------- |
|  5     |  Female   |  2              |
| ------ | --------- | --------------- |
|  ...   |           |                 |

How can I select from two tables as follows :

1. What is your name?

George

Sarah

Michael

2. What is your gender?

Male

Female

"JOIN" makes repetitive questions.

Where is my code wrong?

My controller :

/* controller */
$data['questions'] = $this->db->get('questions_tbl')->result();
        $items_arr = array();
        foreach ($data['questions'] as $option) {
            $items_arr[] = $this->db->where('o_question_id', $option->q_id)->get('options_tbl')->result();
        }
        $data['options'] = $items_arr;

And my view :

/* view */
<?php foreach ($questions as $q) { ?>
    <strong><?= $q->q_id ?>.<?= $q_title ?></strong>
    <?php foreach ($options as $o) { ?>
        <?php if ($o->o_question_id == $q->id) { ?>
            <p><?= $o->o_title ?></p>
        <?php } ?>
    <?php } ?>
<?php } ?>

Solution

You will need to first select the questions, then for each question ID, select it's choices. Below is an example:

function questions(){
   $qtns = array();
    $query = $this->db->get('questions_table');
    foreach($query->result_array() as $one){
        $one['choices'] = $this->questionChoices($one['id'];
        $qtns[] = $one:
    }
  return $qtns;
}

function questionChoices(int $qtnID){
    $this->db->where('o_questions_id', $qtnID);
    $query = $this->db->get('options_table');
     return $query->result_array();
}

Then from your view you can display the questions as:

<?php foreach($qtns as $one){ ?>
  <strong><?= $one['q_id'] ?>.<?= $one['q_title'] ?></strong>
    <?php foreach ($one['choices'] as $o) { ?>
            <p><?= $o['o_title'] ?></p>
        <?php } ?>
<?php } ?>

Answered By - Coding Sniper