Is there a way to display data with foreign key related to the primary key of another table?

Question

I am making a quiz app backend module. I am trying to edit question and answers, how can I edit Questions and the answers of that related question will show?

I have two tables questions and answers table.

Question table structure

id | question

Answers table structure

id | answer | question_id

So question table must be connected to question_id to fetched it answers.

Right now I have this bug, where if I click any Questions I want to edit shows all the answers even that particular answer is not related to the question.

Bug or Problem of the app, please see

This Controller will call the model

public function editPost(){
    $result = $this->post_model->showEditPost();
    echo json_encode($result);
}

This Model queries it to show data.

public function showEditPost(){
    // Show answers
    $id = $this->input->get('id');

    $this->db->select('*');
    $this->db->from('answers');
    $this->db->join('questions', 'answers.question_id = questions.id');
    $this->db->where('questions.id', $id);
    $query = $this->db->get();
    if($query->num_rows() > 0){
        return $query->result();
    }else{
        return false;
    }
}

Show Edit Post function ajax script - this script shows the modal data

function showEditPost(){
    $.ajax({
        type: 'ajax',
        url: '<?php echo base_url() ?>posts/editPost',
        async: false,
        dataType: 'json',
        success: function(data){
            var html = '';
            var i;

        for(i=0; i<data.length; i++){
                html +='<input type="text" value="'+data[i].answer+'" class="form-control" /><hr>';
                }

                $('#showEdit').html(html);
            },
            error: function(){
                alert('Could not get Data from Database');
            }
        });
}

This code triggers the Ajax

<div class="modal fade-scale" id="myModal" tabindex="-1" role="dialog">
   <div class="modal-dialog" role="document">
     <div class="modal-content">
       <div class="modal-header">
         <h4 class="modal-title">Modal title</h4>
       </div>
       <div class="modal-body">
       <div class="card">
            <div class="card-header">
                <h4>Update Answers</h4>
            </div>
            <div class="card-body">         
                <form id="myForm">
                    <input type="hidden" name="answer" id="answer" class="form-control" />
                    <div id="showEdit"></div>
                </form>
            </div>
        </div>
       </div>
       <div class="modal-footer">
         <button type="button" id="btnClose" class="btn btn-default">Close</button>
         <button type="button" id="btnSave" class="btn btn-primary">Update</button>
       </div>
     </div><!-- /.modal-content -->
   </div><!-- /.modal-dialog -->
 </div>

To display modal

//edit
$('#showdata').on('click', '.item-edit', function(){
        var id = $(this).attr('data');

        $('#myModal').modal('show');
        $('#myModal').find('.modal-title').text('Edit Question');
        $('#myForm').attr('action', '<?php echo base_url() ?>posts/updatePost');
        $.ajax({
            type: 'ajax',
            method: 'get',
            url: '<?php echo base_url() ?>posts/editPost',
            data: {id: id},
            async: false,
            dataType: 'json',
            success: function(data){

                //$('input#question').val(data.question);
                $('input#answer').val(data.answer);

            },
            error: function(){
                alert('Could not Edit Data');
            }
        });
});

Issue in ```where```, It should like ```$this->db->where('questions.id',$id );```.You mention only field name not where value. — HP371, Mar 14 '20 at 06:27
@HP371 - hello, yes that is my problem can you help me how can I get the value of that id? if I repalced it hard coded 1 then the output is correct. I also tried using hidden input type value of 1 but still not showing any data — , Mar 14 '20 at 06:29
You can pass id in Ajax data, for that follow https://stackoverflow.com/questions/18697034/how-to-pass-parameters-in-ajax-post/35590754 — HP371, Mar 14 '20 at 06:32
@HP371 hello sir, can you refer to my code as well? or post any modification of my code? really trying my best to figure this out still no luck — , Mar 14 '20 at 06:35
There are many solutions available on just search pass edit id in ajax in php or CI.google,https://stackoverflow.com/questions/13715214/jquery-php-pass-the-value-to-ajax-i-just-need-the-id-to-edit-the-file — HP371, Mar 14 '20 at 06:37
@HP371 - the `$id` is this $id = $this->input->get('id'); . and I bring the id to the where clause but still cannot fetch any data — , Mar 14 '20 at 06:37
As your comment, I think you are a beginner in CI/AJAX so first read related tutorial it helps you lot. — HP371, Mar 14 '20 at 06:43
@HP371 -i think this is the best tutorial for learning sir. I just need to move on from this problem figuring it out for hours now — , Mar 14 '20 at 06:45
@AnkitSingh - yes please see above at the very bottom i edited it, that ajax triggers the modal to open and data should be fetched and display — , Mar 14 '20 at 06:52
@AnkitSingh - have you seen the edited code? at the very bottom? — , Mar 14 '20 at 06:54
I deleted my answer as it is not anymore related to the evolution of your question. — Markipe, Mar 14 '20 at 06:59
Yes I can see now but it's not that what i want to see. Can you please provide your full source of html to [pastebin](https://pastebin.com/) — Ankit Singh, Mar 14 '20 at 07:00
@AnkitSingh Hello, here it is, my full source code. [Full Source Code](https://pastebin.com/Xn83w12Z) — , Mar 14 '20 at 07:03
@AnkitSingh - let me know more sir if what more i can help for you to undestand it. thanks!! — , Mar 14 '20 at 07:05
Where is `editPost()`? which calls the model `showEditPost()` method. — Ankit Singh, Mar 14 '20 at 07:32
@AnkitSingh yes sir, that is the controller it's this `public function editPost(){ $result = $this->post_model->showEditPost(); echo json_encode($result); }` then it will call the model `showEditPost()` sorry for not bringin it up, i was too frustrated about the problem — , Mar 14 '20 at 07:37
@AnkitSingh - I edited it above also if you may want to see it. thank you! — , Mar 14 '20 at 07:38
Please stop reposting the same question & please fix the problems you have repeatedly been told about. https://stackoverflow.com/q/60678873/3404097 https://stackoverflow.com/q/60679275/3404097 [mre] — philipxy, Mar 14 '20 at 19:05

Ankit Singh · Accepted Answer · 2020-03-14T10:02:25.930

Replace these 2 javascript functions, type parameter should be get or post in your $.ajax({type:method_here})

add the following to your ajax success:function()

var html = '';
for(var i=0; i<data.length; i++){
    html +='<input type="text" value="'+data[i].answer+'" class="form-control" /><hr>';
}
$('#showEdit').html(html);

1. Function ShowAllQuestions

function showAllQuestions(){
    $.ajax({
        type: 'post',
        url: '<?php echo base_url() ?>posts/showPosts',
        async: false,
        dataType: 'json',
        success: function(data){
            var html = '';
            var i;
            var n=1;

            for(i=0; i<data.length; i++){
                html +='<div class="card">'+
                    '<div class="card-header" style="color:white; background-color:black">'+
                        '<h4>Question</h4>'+
                    '</div>'+
                    '<div class="card-body">'+
                        '<form>'+
                            '<div class="form-group">'+
                                '<label for="exampleFormControlTextarea1"><h5> Question: </h5></label>'+
                                '<textarea class="form-control" name="question" id="question" rows="8">'+data[i].question+'</textarea>'+
                            '</div>'+
                            '<hr>'+
                            '<a href="javascript:;" class="btn btn-info item-edit" data-id="'+data[i].id+'">Edit </a>&nbsp;'+
                        '</form>'+
                    '</div>'+
                '</div><br>';
            }

            $('#showdata').html(html);
        },
        error: function(){
            alert('Could not get Data from Database');
        }
    });
}

2. Function for show answers of particular question

Send the id of question to the controller to get the relevant answers from database. You don't need to send id through data parameter when you using codeigniter. It allows you to pass parameters as url segments.

$this->uri->segment(number_here) can help you to achive this. number_here will be replacing with actual number of segment you want to get.

you can learn more about uri->segment from HERE

//edit
$('#showdata').on('click', '.item-edit', function(){
    var id = $(this).data('id');

    $('#myModal').modal('show');
    $('#myModal').find('.modal-title').text('Edit Question');
    $('#myForm').attr('action', '<?php echo base_url() ?>posts/updatePost');
    $.ajax({
        type: 'POST',
        url: '<?php echo base_url() ?>posts/editPost/' + id,
        async: false,
        dataType: 'json',
        success: function(data){
            console.log(data);
            var html = '';
            for(var i=0; i<data.length; i++){
                html +='<input type="text" value="'+data[i].answer+'" class="form-control" /><hr>';
            }
            $('#showEdit').html(html);
        },
        error: function(){
            alert('Could not Edit Data');
        }
    });
});

Get the id inside your controller not in your model

public function editPost(){
    $id = $this->uri->segment(3) //if you don't know about uri->segment learn from user_guide
    $result = $this->post_model->showEditPost($id);
    echo json_encode($result);
}

Your model should be

public function showEditPost($id){
    // Show answers
    $this->db->select('*');
    $this->db->from('answers');
    $this->db->join('questions', 'answers.question_id = questions.id');
    $this->db->where('questions.id', $id);
    $query = $this->db->get();
    if($query->num_rows() > 0){
        return $query->result();
    }else{
        return false;
    }
}

did it work on your end sir? Still did not display data on my end. Thank you for your response — , Mar 14 '20 at 08:02
thank you, so is there any possibility I can display the data of the answers? still not getting it though. — , Mar 14 '20 at 08:07
- okay sir so I am getting this on console `0: {id: "1", answer: "Sydney", question_id: "1", correct: "0", type_id: "0", …} 1: {id: "1", answer: "Melbourne", question_id: "1", correct: "0", type_id: "0", …} 2: {id: "1", answer: "Canberra", question_id: "1", correct: "1", type_id: "0", …} 3: {id: "1", answer: "Brisbane", question_id: "1", correct: "0", type_id: "0", …}` but i cannot fetched it on the modal. It is also dynamic if I click on the other question it is correctly fetched. but it does not show on modal input texts — , Mar 14 '20 at 08:10
do you have solution for that sir? the input text is not display and data are not showing — , Mar 14 '20 at 08:39
I think your data is being displayed, but you can't see because you have a `` field in your html code where you are trying to add your output. replace `hidden` type with `text`. — Ankit Singh, Mar 14 '20 at 08:46
I've already change it though. I cannot display the array on the modal. that is the last problem i have. kindly help with the last sir. thank you — , Mar 14 '20 at 08:59
check the updated answer and remove the script function `showEditPost()` — Ankit Singh, Mar 14 '20 at 09:08
Thank you!!. please kindly summarize what you've done. please sir I just need to learn from your modifications. — , Mar 14 '20 at 09:20

Is there a way to display data with foreign key related to the primary key of another table?

1 Answers1