join query in CodeIgniter [duplicate]

问题:

This question already has an answer here:

• Displaying data of two tables on the same web page 1 answer

I am using join query in CodeIgniter and can't get it to work. It only displays one table data, but not the other. I am new to CodeIgniter and can't figure this out. Pleas someone help me. Tnanks in advance.

view

<?php foreach ($query as $row): ?>

    <?php echo $row->answerA;?><br>
    <?php echo $row->answerB;?><br>
    <?php echo $row->answerC;?><br>
    <?php echo $row->comment;?><br>
    <?php echo $row->name; ?>

<?php endforeach; ?>  

controller

function getall() {

    $this->load->model('result_model');
    $data['query'] = $this->result_model->result_getall();
    // print_r($data['query']);
    // die();
    $this->load->view('result_view', $data);

}

model

function result_getall() {

    $this->db->select('tblanswers.*,credentials.*');
    $this->db->from('tblanswers');
    $this->db->join('credentials', 'tblanswers.answerid = credentials.cid', 'right outer'); 
    $query = $this->db->get();
    return $query->result();

}

EDIT

The result of

print_r($data['query']);
die();

is an array as below:

Array ( [0] => stdClass Object ( [answerid] => [userid] => [questionid] => [answerA] => [answerB] => [answerC] => [comment] => [cid] => 83 [name] => Edvinas [second_name] => liutvaits [phone] => [email] => [e-mail] ) [1] => stdClass Object ( [answerid] => [userid] => [questionid] => [answerA] => [answerB] => [answerC] => [comment] => [cid] => 84 [name] => Edvinas [second_name] => liutvaits [phone] => [email] => [e-mail] ) [2] => stdClass Object ( [answerid] => [userid] => [questionid] => [answerA] => [answerB] => [answerC] => [comment] => [cid] => 85 [name] => Edvinas [second_name] => Liutvaitis [phone] => [email] => [e-mail] ) [3] => stdClass Object ( [answerid] => [userid] => [questionid] => [answerA] => [answerB] => [answerC] => [comment] => [cid] => 86 [name] => EdvinasQ [second_name] => LiutvaitisQ [phone] => 12345678 [email] => [e-mail] ) [4] => stdClass Object ( [answerid] => [userid] => [questionid] => [answerA] => [answerB] => [answerC] => [comment] => [cid] => 87 [name] => Edvinas [second_name] => Liutvaitis [phone] => 123456 [email] => [e-mail] ) ) 

table structure

credentials

cid(PRIMARY), name, second_name, phone, email

tblanswers

answerid(PRIMARY), userid, questionid, answerA, answerB, answerC.

回答1:

Try with this:

$this->db->join('credentials', 'tblanswers.answerid = credentials.cid');

Or

$this->db->join('credentials', 'tblanswers.answerid = credentials.cid', 'inner');

And print the result to see if is what you want

回答2:

Remove select() tag, as u need all terms.

U can modify code like

$this->db->join('credentials', 'tblanswers.answerid = credentials.cid', 'outer'); 
$query = $this->db->get('tblanswers');
return $query->result();

回答3:

If both of your tables are having the related, then it should work. Why are you using right outer join ? Simply use the Inner Join or any other join and it will work. Kindly read about different types of JOIN here http://www.w3schools.com/sql/sql_join.asp and here http://dev.mysql.com/doc/refman/5.0/en/join.html

Hope this will help.

Thank you

回答4:

What's the primary table you want to query? Well you can try this one, suggested that when using joins, you must specify an alias for each table:

 function result_getall(){

    // if you want to query your primary data from the table 'tblanswers',
    $this->db->select('a.*,b.*'); <-- select what you might want to select
    $this->db->from('tblanswers a');
    $this->db->join('credentials b', 'b.cid = a.answerid', 'left'); 
    $query = $this->db->get();
    return $query->result();

    // if you want to query your primary data from the table 'credentials',

    $this->db->select('a.*,b.*'); <-- select what you might want to select
    $this->db->from('credentials a');
    $this->db->join('tblanswers b', 'b.answerid = a.cid', 'left'); 
    $query = $this->db->get();
    return $query->result();

}