CODEWITHSUNDEEP
phpmysqlcodeigniteractiverecord
braindamage
braindamage

Reputation: 2256

codeigniter active record get query and query without the LIMIT clause

im using active record, its all working ok but i want to set the $data["totalres"] to the total results, i mean, the same query but without the LIMIT

the problem is the previous statements gets unset when you do a query modifier, so i cant even add the $this->db->limit() after i get the results.

any ideas? i think its a bad practice to 'duplicate' the query just to do this

function get_search($start, $numrows, $filter = array())
{    

    ...

    $this->db
    ->select("emp")
    ->from('emp')
    ->join('empr', 'empr.b = empr.id', 'left')
    ->like('code', $code)
    ->limit($numrows, $start);

    ...

    $q = $this->db->get();        

    // number of rows WITHOUT the LIMIT X,Y filter
    $data["totalres"] = ???????;        

    if ($q->num_rows() > 0)
    {        
        $data["results"] = $q->result();
    } else {
        $data["results"] = array();
    }   

    return $data;
}

Upvotes: 16

Views: 24409

Answers (4)

Luke
Luke

Reputation: 11

I would actually suggest the use of CIs query caching.

To use this, you start the cache, build the query without the limits in place. Run your query to get the full count, then apply the limit and run the query to get your list for your page with the offset you need.

The cache will remember the variables that have been defined.

You can then clear the cache for subsequent queries.

Example

    $this->db->start_cache();

    // Where, like, having clauses in here

    $this->db->stop_cache();

    $count = $this->db->get('table')->num_rows();

    $this->db->limit('limit', 'offset');

    $result = $this->db->get('table')->result_array();

    $this->db->flush_cache();

Upvotes: 1

chu.tien
chu.tien

Reputation: 1

$this->db
->select("SQL_CALC_FOUND_ROWS emp", FALSE)
->from('emp')
->join('empr', 'empr.b = empr.id', 'left')
->like('code', $code)
->limit($numrows, $start); $q = $this->db->get();

$query = $this->db->query('SELECT FOUND_ROWS() AS `Count`');
$data["totalres"] = $this->db->get()->row()->Count;

CodeIgniter 2.1.0 not run, below code will fix it.

$query = $this->db->query('SELECT FOUND_ROWS() AS `Count`');
$objCount = $query->result_array();
$data["totalres"] = $objCount[0]['Count'];

Upvotes: 0

gen_Eric
gen_Eric

Reputation: 227240

You can use SQL_CALC_FOUND_ROWS to get the number of rows that would have been returned sans-LIMIT. Note the ,FALSE in the select line. This tells CodeIgniter not to try to escape the SELECT clause with backticks (because SQL_CALC_FOUND_ROWS is not a field, and CodeIgniter doesn't realize that).

$this->db
->select("SQL_CALC_FOUND_ROWS emp", FALSE)
->from('emp')
->join('empr', 'empr.b = empr.id', 'left')
->like('code', $code)
->limit($numrows, $start);

$q = $this->db->get();

Then after that query is ran, we need run another query to get the total number of rows.

$query = $this->db->query('SELECT FOUND_ROWS() AS `Count`');
$data["totalres"] = $query->row()->Count;

Upvotes: 39

Alfonso Rubalcava
Alfonso Rubalcava

Reputation: 2247

Try this:

function get_search($start, $numrows, $filter = array()){    
    $tmp= $this->db
    ->select("emp")
    ->from('emp')
    ->join('empr', 'empr.b = empr.id', 'left')
    ->like('code', $code)
    ->_compile_select();

    $q= $this->db->limit($numrows, $start)->get();

    // number of rows WITHOUT the LIMIT X,Y filter

    $data["totalres"] = $this->db->query($tmp)->num_rows();

    if ($q->num_rows() > 0){        
        $data["results"] = $q->result();
    } else {
        $data["results"] = array();
    }   
    return $data;
}

Upvotes: 1

Related Questions