How to SET and access a SQL variable with CodeIgniter?

Question

I need to execute this query with Codeigniter query builder:

SET @row_number:=0;

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

I can do it by using "$this-> db-> query" function like this:

$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 it without hard write the query, so writing something like this:

$query = 'SET @row_number:=0;';
$this->db->query ($query);
#*****It's 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();

Saty · Accepted Answer · 2015-12-26 13:24:30Z

2

No need to pass array in select you just write your query as

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

answered Dec 26, 2015 at 13:24

Saty

22.5k7 gold badges35 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fabio Over a year ago

It doesn't work because generated query is like this: SELECT 'Id', '@row_number:=@row_number+1' as Position FROM 'my_table'. I think there are two problems: 1-It doesn't consider the first part of query (SET @row_number:=0). 2-Using your solution it attaches quotes like '@row_number:=@row_number+1'

Bart Az. · Accepted Answer · 2019-09-21 01:09:42Z

Personally, I would prefer to order the results by some column and add the desired position in a foreach loop, cause it's more efficient and it keeps the code cleaner imo, nevertheless it is possible.

The key to the solution is the second, optional parameter of the CI db select function, as it is pointed out in the user guide:

$this->db->select() accepts an optional second parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names. This is useful if you need a compound select statement where automatic escaping of fields may break them.

Like so:

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

Collectives™ on Stack Overflow

How to SET and access a SQL variable with CodeIgniter?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related