CODEWITHSUNDEEP
codeigniter
ShoeLace1291
ShoeLace1291

Reputation: 4698

Can I run a CodeIgniter database query without clearing it from the class?

I am using the CodeIgniter database class to generate results and then use the pagination class to navigation through them. I have a model that retrieves member results from the table. I would like to calculate the total number of rows from the query so I can pass this to the pagination class. The count_all db helper function won't suffice because that doesn't take in account any "where" or "join" clauses that I include.

If this is my query:

$this->db->select('m.user_id AS id, m.email_address, m.display_name, m.status, UNIX_TIMESTAMP(m.join_date) AS join_date,
            l.listing_id, COUNT(l.member_id) AS total_listings,
            g.group_id AS group_id, g.title AS group_title')
    ->from('users AS m')
    ->join('listings AS l', 'm.user_id = l.member_id', 'left')
    ->join('groups AS g', 'm.group_id = g.group_id', 'left')
    ->group_by('m.user_id');

How can I continue to use this query if I wanted to do something like this:

if($query_total = $this->db->get()){

   $this->total_results = $query_total->num_rows();

}

$this->db->limit($limit, $offset);
if($query_members = $this->db->get()){

   return $query_members->result_array();
}

Update: In other words, I want to run a query with the get() method without clearing it from the query builder when it's done running so I can use the top part of the query later.

Upvotes: 0

Views: 87

Answers (2)

Arkar Aung
Arkar Aung

Reputation: 3584

You can get it by using Active Record Caching (the last category on that page).

$this->db->start_cache(); // start caching
$this->db->select('m.user_id AS id, m.email_address, m.display_name, m.status, UNIX_TIMESTAMP(m.join_date) AS join_date,
        l.listing_id, COUNT(l.member_id) AS total_listings,
        g.group_id AS group_id, g.title AS group_title')
->from('users AS m')
->join('listings AS l', 'm.user_id = l.member_id', 'left')
->join('groups AS g', 'm.group_id = g.group_id', 'left')
->group_by('m.user_id');
$this->db->stop_cache(); // stop caching

Then you can use that query as much as you want.

if($query_total = $this->db->get()){

   $this->total_results = $query_total->num_rows();

}

$this->db->limit($limit, $offset);
if($query_members = $this->db->get()){

   return $query_members->result_array();
}

You can also flush the cache when you don't want that cached query anymore.

$this->db->flush_cache();

Hope it will be useful for you.

Upvotes: 1

PRATEEK SINGH
PRATEEK SINGH

Reputation: 9

You are not passing any table name in $this->db->get() function. Thank you

Upvotes: 0

Related Questions