Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Code igniter always escaping my SELECT ... CASE query

Tags:

codeigniter

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?

like image 384
Jonathan Avatar asked Feb 19 '16 09:02

Jonathan


People also ask

How to use SQL query in CodeIgniter?

$sql = "INSERT INTO table (title) VALUES('". $this->db->escape_str($title). "')"; $this->db->escape_like_str() This method should be used when strings are to be used in LIKE conditions so that LIKE wildcards ('%', '_') in the string are also properly escaped.

How to run query in CodeIgniter 4?

Once you have a prepared query you can use the execute() method to actually run the query. You can pass in as many variables as you need in the query parameters.

What is query builder in CodeIgniter?

CodeIgniter gives you access to a Query Builder class. This pattern allows information to be retrieved, inserted, and updated in your database with minimal scripting. In some cases only one or two lines of code are necessary to perform a database action.


1 Answers

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.

like image 83
AdrienXL Avatar answered Nov 22 '22 10:11

AdrienXL