Fetching the data from the database in PHP using loop

Question

I want to show the questions and their respective answers in the view from the database:

I have got the database table named Feedback in the following form:

This is my Table Name: Feedback

id | employee_id | question_id |   answer            |
______________________________________________________

1  |   100       |   1         | That was awesome. 
2  |   100       |   2         | That was excellent.  
3  |   100       |   3         | That was good.  
4  |   101       |   1         | That was better.  
5  |   101       |   2         | That was interesting.  
6  |   101       |   3         | That was fine.

And another table named with Questions:

id |      Question        |
_______________________________________________

1  | How was your day? 
2  | How was the task?
3  | How was the Project?

Here is my model's code :

function get_answers_supervisee () {
    $userid = $this->session->userdata('userid');
    $result = array ();
    $sql = "select answer from Feedback where employee_id = '$userid'";
    $query = $this->db->query ( $sql );
    foreach ( $query->result_array () as $row ) {
        array_push ( $result, $row );
    }
    return $result; 
}

Below is the my Form (Client Side View):

<table class="table">       
     <?php foreach($form_data_supervisee as $question) {?>
     <tr>      
         <td>
         <?php echo $question['ID'].". ".$question['DESCRIPTION']?>
         </td>
    </tr>   
         <?php foreach($form_answers_supervisee as $answer) {?>                 
    <tr>   
          <td>
          <textarea rows="5" name="<?php echo $answer['ANSWER']?></textarea>
          </td> 
    </tr>   
    <?php } ?>  
    <?php }?> 
  </table>

Here is the part of my controller:

 $data['form_answers_supervisee'] = $this->appraisal_model->get_answers_supervisee();
     $data['form_answers_supervisor'] = $this->appraisal_model->get_answers_supervisor();

     $this->load->view('main_form',$data);

Now I am getting the following output for employee having employee_id: 100

1. How was your day?
   That was awesome. 
   That was excellent.
   That was good.

2. How was the task?
   That was awesome. 
   That was excellent.
   That was good.

3. How was the Project?
   That was awesome. 
   That was excellent.
   That was good.

The required output should be:

1. How was your day?
   That was awesome. 

2. How was the task?
   That was excellent.

3. How was the Project?
   That was good.

What correction do I need here? What am I doing wrong?

Suggestions are highly appreciated.

I just updated the question and the table named from appraisal_store to Feedback. I wanted to as ask question in the simple form so, had to change the db content and table name in question and had forgotten to change the table name in the question. — Dixon Chaudhary, Aug 06 '18 at 05:39

Atural · Answer 1 · 2018-08-06T05:49:36.873

try the following

your model

function get_answers_supervisee () 
{
    $userid = $this->session->userdata('userid');

    $query = $this->db
        ->select('f.*, q.id AS question_id, q.Question')
        ->from('Feedback f')
        ->join('Questions q', 'f.question_id = q.id', 'left')
        ->where('employee_id', $userid)
        ->get();

    return $query->result();
}

Controller should stay the same.

Your view

<table class="table">       
     <?php 
     foreach($form_data_supervisee as $question) {
     ?>
     <tr>      
         <td>
         <p><?php echo $question->question_id.". ".$question->Question; ?></p>
         <p>Answer: <?php echo $question->answer; ?></p>
         </td>
    </tr>   
    <?php 
    }
    ?> 
</table>

As an additional note:

Your query is fully open to sql injections - you really should use the built-in QueryBuilder for those purposes and for your own good.

Take a look here https://www.codeigniter.com/userguide3/database/configuration.html#query-builder and activate the query builder, if you didn't already.

score 1 · Answer 2 · answered Aug 06 '18 at 05:50

Just add an if statement in your view to output answer according to question.

 <table class="table">       
     <?php foreach($form_data_supervisee as $question) {?>
     <tr>      
         <td>
         <?php echo $question['ID'].". ".$question['DESCRIPTION']?>
         </td>
    </tr>   
         <?php foreach($form_answers_supervisee as $answer) {?>                 
    <tr>   
          <!-- check "id" in  "questions" table matches the "question_id" in "feedback" table --> 
          <?php if($question['ID'] == $answer['question_id']){ ?>
              <td>
                    <textarea rows="5" name="<?php echo $answer['ANSWER']?></textarea>
              </td>
          <?php } ?> 
    </tr>   
    <?php } ?>  
    <?php }?> 
  </table>

Pradeep · Accepted Answer · 2018-08-06T06:22:17.557

1

Hope this will help you :

You have to add a join query based on questions table with feedback table like this :

function get_answers_supervisee () 
{
    $employee_id = $this->session->userdata('userid');
    $this->db->select('q.id , q.question,f.answer ,f.employee_id');
    $this->db->from('questions q');
    $this->db->join('feedback f','f.question_id = q.id');
    $this->db->where('f.employee_id', $employee_id);
    $result = $this->db->get()->result();
    return $result;
    //print_r($result);
}

The output looks like :

id  question                 answer           employee_id
1   How was your day?     That was awesome.     100
2   How was the task?     That was excellent.   100
3   How was the Project?  That was good.        100

Your view access table column like this :

<table class="table">       
     <?php foreach($form_data_supervisee as $item) {?>
     <tr>      
         <td>
         <?php echo $item->id;?>
         </td>
         <td>
         <?php echo $item->quesion;?>
         </td>
         <td>
         <?php echo $item->answer;?>
         </td>
      </tr>  

    <?php }?> 
</table>

edited Aug 06 '18 at 06:22

answered Aug 06 '18 at 06:08

Pradeep

9,667
13
27
34

When I run the query generated in the SQL Developer then I am getting this error: ORA-00904: "q"."ID": invalid identifier 00904. 00000 - "%s: invalid identifier" – Dixon Chaudhary Aug 06 '18 at 07:33
make sure your id column in questions table is `id` not `ID` , i not use `ID` field anywhere in my code – Pradeep Aug 06 '18 at 07:42
I think there is Oracle Active Record Supporting Issue due to version of the Code Igniter. I am using the CodeIgniter version 3.1.4 – Dixon Chaudhary Aug 06 '18 at 08:41
Thank you so much pradeep sir for the valuable feedback. I got my solution. You are such an amazing person. – Dixon Chaudhary Aug 06 '18 at 10:39
pleasure is mine happy coding – Pradeep Aug 06 '18 at 10:40

score 0 · Answer 4 · answered Aug 06 '18 at 05:54

hello your query must be based on question_id

    <?php 

    function get_answers_supervisee ($question_id) {
        $userid = $this->session->userdata('userid');
        $result = array ();
        $sql = "select answer from Feedback where employee_id = '$userid' AND question_id = '$question_id' LIMIT 1";
        $query = $this->db->query ( $sql );
        foreach ( $query->result_array () as $row ) {
            array_push ( $result, $row );
        }
        return $result; 
    }

    ?>

I just used this approach and got the error on line: $query = $this->db->query ( $sql ); — Dixon Chaudhary, Aug 06 '18 at 06:01

Fetching the data from the database in PHP using loop

4 Answers4