PHP Fatal error: Call to a member function row() on a non-object result_array() when I use SQL subquery

Question

I am using WAMP server 2.4. I am trying to get count of distinct months in database but getting this error "Call to a member function row() on a non-object result_array()". This is my code for my model class

public function get_current_month()
{

    $this->db->select('(COUNT(DISTINCT  Month))' );
    $this->db->from('site_data');
    $this->db->where('year =(');
    $this->db->select('MAX(year )');
    $this->db->from('site_data)');
    $query = $this->db->get();


    return $query->result_array(); //error here

}

I believe this has something to do with sub query in code because when I use this

public function get_current_month()
{

    $this->db->select('(COUNT(DISTINCT  Month))' );
    $this->db->from('site_data');
    $this->db->where('year =2013');
    //$this->db->select('MAX(year )');
    //$this->db->from('site_data)');
    $query = $this->db->get();


    return $query->result_array();

}

it is working just fine. I am using codeigniter

Does anyone has idea what is going wrong here.

This is the query which I tested in database it is working perfectly

SELECT (COUNT(DISTINCT MONTH))
FROM site_data
WHERE YEAR = ( 
    SELECT MAX(YEAR) 
    FROM site_data
)

It doesn't work that way with CI. Please take a look at [this post](http://stackoverflow.com/questions/6047149/subquery-in-codeigniter-active-record) as it might help you on how to include subqueries. — geomagas, Oct 10 '13 at 18:21
`$this->db->select()` accepts an optional second parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks. This is useful if you need a compound select statement.http://ellislab.com/codeigniter/user-guide/database/active_record.html#select — Arun Killu, Oct 10 '13 at 18:41

score 0 · Answer 1 · edited May 23 '17 at 12:05

0

It looks as though you either need to hand-craft your subquery into a ->where() call or employ some kind of third-party library. See subquery in codeigniter active record which deals with a similar issue.

edited May 23 '17 at 12:05

Community

1
1

answered Oct 10 '13 at 18:23

Lancetarn

396
1
8

score 0 · Answer 2 · answered Oct 10 '13 at 20:04

I have only a passing experience of CI but it looks pretty clear that the way you are trying to call a subquery by nesting a db->select etc. isn't quite right. There may be a way of building up the subquery but...

For simplicity's sake can you try this:

public function get_current_month()
{

    $this->db->select('(COUNT(DISTINCT  Month))' );
    $this->db->from('site_data');
    $this->db->where('year =( SELECT MAX(year) FROM site_data)');
    $query = $this->db->get();


    return $query->result_array(); //error here

}

If it works, why make it more complicated?

score 0 · Answer 3 · answered Oct 11 '13 at 02:14

Thanks a lot guys. I figured it will be easy to add a function that gets the MAX(year) from database and then pass the value to my function.

I solved it like this

public function get_current_month($Year)
{
    $MAXYear=$Year;
    $this->db->select('(COUNT(DISTINCT  Month))' );
    $this->db->from('site_data');
    $this->db->where('year',$MAXYear);
    $query = $this->db->get();

    return $query->result_array();

}

where year is the value I am passing from controller to get_current_month().

It works like charm.

Thanks a lot for all of your suggestions.

PHP Fatal error: Call to a member function row() on a non-object result_array() when I use SQL subquery

3 Answers3