Codeigniter SET parameter in request

Question

I need to execute this query using the Codeigniter query constructor:

SET @row_number:=0; SELECT Id, @row_number: =@row _number+1 as Position FROM my_table WHERE date='2015-12-26' 

I can do this using the function "$ this-> db-> query" as follows:

  $query = 'SET @row_number:=0;'; $this-> db-> query ($query); $query = 'SELECT Id, @row_number: =@row _number+1 as Position'; $query = $query . ' FROM my_table'; $query = $query . ' WHERE date=\'' . $data . '\'' 

But, my question is: is there a way to do this without a complicated record of the request, so I'm writing something like this:

  $query = 'SET @row_number:=0;'; $this-> db-> query ($query); #*****It wrong!!!!***** $this -> db -> select(array('Id', '@row_number: =@row _number+1 as Position')); $this -> db -> from('my_table'); $this -> db -> where('date', $data); $query = $this -> db -> get(); 

Answer 1

No need pass array in select just write your query as

  $this -> db -> select('Id, @row_number: =@row _number+1 as Position'); 

Answer 2

Personally, I would prefer to sort the results by a certain column and add the desired position to the foreach loop, make it more efficient and keep the code clean, although it is possible.

The key to the solution is the second, optional parameter of the CI db selection function, as indicated in the user manual :

$ this-> db-> select () takes an optional second parameter. If you set the value to FALSE, CodeIgniter will not attempt to protect your field or table names. This is useful if you need a compound select statement in which automatically escaping fields can violate them.

Like this:

 $query = 'SET @row_number := 0'; $this->db->query($query); $this->db->select(array('Id', '@row_number: =@row _number+1 as Position'), false); $this->db->from('my_table'); $this->db->where('date', $data); $query = $this->db->get();