CODEWITHSUNDEEP
phpmysqlcodeigniteractiverecordescaping
Dinesh
Dinesh

Reputation: 350

Automated quoting in CodeIgniter select() method is corrupting SELECT clause

I have this query:

$this->db->select('
    COUNT(tran_ID) AS count,
    CASE WHEN MONTH(days)>=4
              THEN concat(YEAR(days), "-", YEAR(days)+1)
         ELSE
              concat(YEAR(days)-1, "-", YEAR(days))
    END AS format_date,
    product_class AS saleCol
');

I get backticks in the incorrect places and a syntax error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHEN MONTH(days)>=4 THEN concat(YEAR(days), `"-"`, YEAR(days)+1) ELSE concat(YEA' at line 1

SELECT COUNT(tran_ID) AS count, `CASE` WHEN MONTH(days)>=4 THEN concat(YEAR(days), `"-"`, YEAR(days)+1) ELSE concat(YEAR(days)-1, `"-"`, YEAR(days)) END AS format_date, `product_class` AS saleCol FROM (`transactions`) WHERE `trans_type` = 'E' AND `product_class` != '0' GROUP BY `format_date`, `product_class`

How can I prevent these quoting errors?

Upvotes: 0

Views: 886

Answers (1)

iLaYa ツ
iLaYa ツ

Reputation: 4017

Try this query, added FALSE as the second parameter in select statement

$this->db->select(
                  'COUNT(tran_ID) AS count, 
                   CASE WHEN MONTH(days)>=4 THEN concat(YEAR(days), "-", YEAR(days)+1)
                     ELSE concat(YEAR(days)-1, "-", YEAR(days)) 
                   END AS format_date,
                   product_class AS saleCol',FALSE);

Upvotes: 1

Related Questions