Code igniter always escaping my SELECT ... CASE query

Question

I am using Codeigniter to fire a query on my db. The code looks like:

$this->db->select("category.Name,
booking.Comment,
CASE WHEN amount > 0 THEN amount END AS PosAmount,
CASE WHEN amount < 0 THEN amount END AS NegAmount");

But I always get an

You have an error in your SQL syntax ... right syntax to use near
'WHEN amount > 0 THEN amount END AS PosAmount, `CASE` WHEN amount < 0 
THEN amount' at line 1

Codeigniter is escaping the CASE but I do not know how to prevent this.

Any ideas?

$query=$this->db->query("category.Name, booking.Comment, CASE WHEN amount > 0 THEN amount END AS PosAmount, CASE WHEN amount < 0 THEN amount END AS NegAmount"); $query->result(); — Aslam Patel
– Aslam Patel, Commented Feb 19, 2016 at 9:13
This question is similar to: How to write a SELECT clause containing an SQL function call with CodeIgniter's select() method. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. — mickmackusa
– mickmackusa ♦, Commented Jun 4 at 17:48

AdrienXL · Accepted Answer · 2016-02-19 11:20:43Z

8

From the documentation :

$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.

In your case :

$this->db->select("category.Name,
booking.Comment,
CASE WHEN amount > 0 THEN amount END AS PosAmount,
CASE WHEN amount < 0 THEN amount END AS NegAmount", FALSE);

This second parameter can also be used in where and join clauses.

answered Feb 19, 2016 at 11:20

AdrienXL

3,0083 gold badges25 silver badges40 bronze badges

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

Collectives™ on Stack Overflow

Code igniter always escaping my SELECT ... CASE query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related