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
– Dixon Chaudhary, Commented Aug 6, 2018 at 5:39

Atural · Accepted Answer · 2018-08-06 05:49:36Z

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.

Pradeep · Accepted Answer · 2018-08-06 06:22:17Z

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 6, 2018 at 6:22

answered Aug 6, 2018 at 6:08

Pradeep

9,70713 gold badges29 silver badges34 bronze badges

5 Comments

Dixon Chaudhary Over a year ago

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"

Pradeep Over a year ago

make sure your id column in questions table is id not ID , i not use ID field anywhere in my code

Dixon Chaudhary Over a year ago

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 Over a year ago

Thank you so much pradeep sir for the valuable feedback. I got my solution. You are such an amazing person.

Pradeep Over a year ago

pleasure is mine happy coding

Abdul Rehman Mehar · Accepted Answer · 2018-08-06 05:50:31Z

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>

Afshin · Accepted Answer · 2018-08-06 05:54:24Z

0

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; 
    }

    ?>

answered Aug 6, 2018 at 5:54

Afshin

12 bronze badges

1 Comment

Dixon Chaudhary Over a year ago

I just used this approach and got the error on line: $query = $this->db->query ( $sql );

Collectives™ on Stack Overflow

Fetching the data from the database in PHP using loop

4 Answers 4

Comments

5 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

5 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related