Codeigniter-returns only one row from my model class

Question

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 rows! What could be the reason?Why its returning only one rows?

Please help me regarding this problem

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

score 2 · Accepted Answer · answered Apr 14 '13 at 08:37

2

You have a COUNT() sql aggregation in the select. Since you don't have any GROUP BY, the database will use the whole result set as one implicit group thus reducing your resultset to one row when counting it all up. This is how sql should work.

You can check out the generated sql query with print $this->db->last_query() right after the $this->db->get(); line, and run it in your sql console to see what's happening.

You probably wanted to add a $this->db->group_by('question.question_id'); or something similar.

answered Apr 14 '13 at 08:37

complex857

20,425
6
51
54

i kept your group_by code, but still it is returning one row. My `db->last_query() returns this sql `SELECT `question`.*, `userdetails`.*, COUNT(answer.answer_id) AS `Answers` FROM (`question`) JOIN `answer` ON `answer`.`question_id` = `question`.`question_id` JOIN `userdetails` ON `userdetails`.`user_id` = `question`.`user_id` GROUP BY `answer`.`question_id` LIMIT 5` – Dan Apr 14 '13 at 08:54
Looks like you have only one distinct question_id in the answer table than. I can't really tell without your actual data. You could try to build your query with sql first and chop it up to CI's method calls when it does what you want. Also, keep in mind that selecting non aggregated columns with group by -s are probably non-predictable, see http://stackoverflow.com/a/1591976/1515540. – complex857 Apr 14 '13 at 09:03
Actually,its not working ,now i found the problem why its not showing me the other row of my `question` table. It is showing only the question which have answer .Those question which have no answer it is not counting. how can i modify my above code to meet my new requirement? – Dan Apr 14 '13 at 12:27
solved its like `$this->db->join('answer','answer.question_id = question.question_id', 'left');` – Dan Apr 14 '13 at 12:53
I'm glad that you could sort this out even trough i guessed your original intent wrong (-: – complex857 Apr 14 '13 at 15:47

Codeigniter-returns only one row from my model class

1 Answers1