Mysql case on codeigniter active records

Mysql case on codeigniter active records - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Mysql case on codeigniter active records (/showthread.php?tid=46834)

Mysql case on codeigniter active records - El Forum - 11-16-2011

[eluser]Djaka PM[/eluser]
Hi,

I used query on active records, that uses mysql case, but it seems the query never work
because active records add a quote in the "case" keyword, here are the code:

Code:
$this->db->select('orl.id,case when ord.name is null then orc.name else ord.name end as name');

  $this->db->from('ongkir_ref_location orl');

  $this->db->join('ongkir_ref_district ord','ord.id = orl.district_id','left');

  $this->db->join('ongkir_ref_city orc','orc.id = orl.city_id','inner');

  $this->db->where(array('orl.id'=>$location_id));

the output is:

Code:
Error Number: 1064

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 ord.name is null then orc.name else ord.name end as name FROM (`ongkir_ref_' at line 1

SELECT `orl`.`id`, `case` when ord.name is null then orc.name else ord.name end as name FROM (`ongkir_ref_location` orl) LEFT JOIN `ongkir_ref_district` ord ON `ord`.`id` = `orl`.`district_id` INNER JOIN `ongkir_ref_city` orc ON `orc`.`id` = `orl`.`city_id` WHERE `orl`.`id` = '2272'

I have tried to run the questioned query on phpMyAdmin, it will only runs if I removed the
qoute/backtick on the "case" keyword.

is this a correct behaviour of active records?

Because when I created a sql string and execute it using:

Code:
$sql = "select orl.id,case when ord.name is null then orc.name else ord.name end as name ";

  $sql .= "from ongkir_ref_location orl ";

  $sql .= "left join ongkir_ref_district ord on ord.id = orl.district_id ";

  $sql .= "inner join ongkir_ref_city orc on orc.id = orl.city_id ";

  $sql .= "where orl.id=".$this->db->escape($location_id);

$this->db->query($sql)

It work fine.

Thank You...