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

Question

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.

Answer 1

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();