CodeIgniter with SELECT COUNT() and no GROUP BY clause only returns one row
02:22 14 Apr 2013

I have written a model code where I am joining two tables, and returning my results.

I have 26 results in my table, but the code below I mention is returning only one row! What could be the reason? Why is it returning only one row?

Update

Table structure

question
-----------
question_id PK Auto_Incr  
question    varchar... 
votes       int

answer
------------
answer_id    PK  Auto_icre
question_id  FK refrences question  
content      longtext

From the below table structure my model code is showing only 2 question count, skipping the last question, After little research, I found the reason why it is not counting my third question, it is because it does not have any answers in my answer table.

I want, if no answer then it should show count=0 for the particular question, How can to solve this issue?

Table Data structure data:

 question
-----------
 question_id    question          votes
    1           what's name?       0
    2           where you?         3
    3           blah blah          9 

answer 
----------
 answer_id      question_id        content
    4              2                 India
    5              2                 Nepal
    6              2                 Pakistan
    7              1                 Mr Osama Binladan

Model

public function fetch_allquestions($limit, $start) 
{
    $this->load->database(); 
    $this->db->limit($limit, $start);   
    $this->db->from('question');
    $select = array(
        'question.*',
        'userdetails.*',
        'COUNT(answer.answer_id) AS `Answers`'
    );
    $this->db->select($select);

    $this->db->join('answer', 'answer.question_id = question.question_id'); 
    $this->db->join('userdetails', 'userdetails.user_id = question.user_id'); 
    $query = $this->db->get();

    print_r("Number of rows=" . $query->num_rows());//showing only One, out of 26 rows

    if ($query->num_rows() > 0)
    {
        foreach ($query->result() as $row)
        {
            $data[] = $row;
        }
        return $data;
    } else {
        return false;
    }
}
php mysql codeigniter join count