CODEWITHSUNDEEP
phpcodeigniteractiverecord
Rahul
Rahul

Reputation: 13056

How to reset/clear order-by between two consecutive queries in CodeIgniter?

I am trying to call 2 consecutive queries in CodeIgniter, the first of which has an order-by clause. The trouble is, CodeIgniter is trying to use the order-by clause for the second query as well and throwing an error.

The code looks something like this:

 ...
 $sql = $this->db->get_where('expenses',array('category_id' => $category_id));
 $this->db->order_by("date", "asc");
 $data = $sql->result_array();

 foreach($data as $expense_rec)
 {
     $expense_id = $data['expense_id'];
     $sql2 = $this->db->get_where('expense_details',array('expense_id' => $expense_id));
     $detail_rec = $sql2->result_array();
 }
 ...

For the second query, the script throws the following error: 

Unknown column 'date' in 'order clause'

SELECT * FROM (`expense_details`) WHERE `expense_id` = '4' ORDER BY `date` asc

Is there a way I can reset the order-by before calling the second query?

I am using CodeIgniter 1.7

P.S. I know that I can join the two queries into one but I am curious to know if there is a way for the above code to work in CodeIgniter.

Upvotes: 4

Views: 7827

Answers (3)

user2278752
user2278752

Reputation: 41

I ran into a similar issue. In my case, I'm building a query based on user input. Halfway into building the query, there may be an issue which causes me to stop building the query and exit the model. However, if other models are called after this with other Active Record calls, the previously built queries are added to, which is definitely not my intention.

What I did to solve this problem is copy the DB object to a new variable, and use that for my query building. That variable only exists within that model anyway, so is destroyed before it can affect other models. So:

$new_db = $this->db;
$new_db->where('user_status', 1);

etc.

Upvotes: 2

Femi
Femi

Reputation: 64710

You can use $this->db->_reset_select() to clear things out.

Upvotes: 2

jondavidjohn
jondavidjohn

Reputation: 62412

Running the query is what resets it.

You are placing the order_by() after you run the query, so it is not affecting the inital query, but it starts building the next query.

instead of...

$sql = $this->db->get_where('expenses',array('category_id' => $category_id));
$this->db->order_by("date", "asc");
$data = $sql->result_array();

do this...

$this->db->order_by("date", "asc");
$sql = $this->db->get_where('expenses',array('category_id' => $category_id));
$data = $sql->result_array();

Upvotes: 9

Related Questions