Codeigniter - how to make second MySQL query using conditions ofp revious one

Question

Hey I have the following problem:

I have a very long MySQL query, and then I want to count rows which it returns.

After getting this result I want to make it again with some limit and group my results by one of columns.

So it looks that:

/* 
query conditions....
query conditions....
query conditions....
query conditions....
query conditions....
query conditions....
query conditions....
*/

//first query

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

$results = $query->result_array(); 

$this->db->select("FOUND_ROWS() as cnt");

$cnt_array = $this->db->get()->row_array(); //here is my number of rows


//second query and if instruction


        if($sth==0) { $this->db->group_by(...);}


        $this->db->limit($count, $from);


        $query = $this->db->get();
        $results = $query->result_array();

        $this->db->select("FOUND_ROWS() as cnt");

        $tot_cnt = $this->db->get()->row_array(); //now i want to have number of grouped results.

but the second query doesnt work, how to do it?

Look at this answer which will give you a fairly good idea http://stackoverflow.com/questions/2439829/how-to-count-all-rows-when-using-select-with-limit-in-mysql-query — Muhammad Raheel, Jun 20 '12 at 11:54
My query is really long, that's the point of restoring information from previous query and just add two instructions (limit, group by) — pawel, Jun 20 '12 at 12:29

score 1 · Answer 1 · answered Jun 20 '12 at 11:49

You can write multiple queries like this

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

$results = $query->result_array(); 

$sth = $this->db->select("FOUND_ROWS() as cnt");

if($sth){
    $qry2 =  $this->db->get();
    $result2 = $qry2->result_array(); 

    $sth2 = $this->db->select("FOUND_ROWS() as cnt");
}

score 1 · Accepted Answer · answered Jun 20 '12 at 13:03

I'm a little confused by your question. If you are wanting to simply run EXACTLY the same query, but sometimes with two extra commands - then this will do it.

If you want to run the 2nd query using the RESULTS of the first query - then this wont help

function _generic_query()
{
    /* 
    query conditions....
    query conditions....
    query conditions....
    query conditions....
    query conditions....
    query conditions....
    query conditions....
    */
return;
}

function first_query()
{
    $this->_generic_query();

    $query = $this->db->get();
    $results = $query->result_array(); 
    $this->db->select("FOUND_ROWS() as cnt");
    $cnt_array = $this->db->get()->row_array(); //here is my number of rows
}

function second_query()
{
    $this->_generic_query();

    if($sth==0) { $this->db->group_by(...);}
    $this->db->limit($count, $from);
    $query = $this->db->get();
    $results = $query->result_array();
    $this->db->select("FOUND_ROWS() as cnt");
    $tot_cnt = $this->db->get()->row_array();
}

Codeigniter - how to make second MySQL query using conditions ofp revious one

2 Answers2