CodeIgniter 2.0.3中Active Record中奇怪的反引号行为 [英] Weird backticks behaviour in Active Record in CodeIgniter 2.0.3
问题描述
以前,我所有的查询在CI版本2.0中运行都很好,但是当我升级到 2.0.3 时,我的某些SELECT查询就被破坏了。 p> CI自动添加了反引号(``),但在旧版本中,它按原样运行。
CI用户手册已指示在
db-> select
中添加第二个参数
as
FALSE
但是仍然无法正常工作。
代码如下:
class Company_model扩展MY_Model
{
----------------
$ this-> db-> select('count('。$ fieldname。')as num_stations');
$ this-> db-> select( CONCAT_WS(’,’,clb_company.address1,clb_company.address2,clb_company.city,clb_company.state,clb_company.zipcode)作为companyAddress);
$ this-> db-> from($ this-&__ table);
$ this-> db-> join($ this-> _table_device,$ fieldname1。 =。$ fieldname2,'LEFT');
$ this-> db-> where($ blablafield,'0');
----------------
错误如下:
错误号:1064
您的SQL语法有错误;检查与您的MySQL服务器版本相对应的手册以获取正确的语法,以在
附近使用。'FROM(`clb_device`)JOIN`clb_company` ON`clb_company`.`id` =`clb_device`.com 2
选择`clb_device`.`id作为deviceId,`clb_pricing_specifications`.`name`作为pricesingSpecName,`clb_company`.`name`作为companyName,`clb_device`.`mac_address`作为deviceMacAddress,
`clb_device`.reseller_model_number`作为deviceModelNumber,`clb_pricing_spec_grouping`.`pricing_master_spec_id`作为pricesingSpecId,`clb_device`.`address`作为deviceAddress,
`clb_device`.`is_home`作为deviceIsHomeCharger,CONCAT( clb_company.portal_line1,`/',`clb_device`.`name)`作为deviceDisplayName FROM(`clb_device`)JOIN`clb_company`
on`clb_company`.`id` =`clb_device`.`company_id` LEFT JOIN`clb_pricing_group_devices` ON`clb_device`.`id` =`clb_pricing_group_devices.isdevice_id`和clb_pricing_group_devices.is_active = 1
LEFT JOIN`clb_pricing_spec_grouping` ON`clb_pricing_groing pricing_spec_id` =`clb_pricing_spec_grouping`.`pricing_master_spec_id`左联接`clb_pricing_specifications` ON
`clb_pricing_spec_grouping`.pricing_spec_id` =`clb_pricing_specifications`id id。 (clb_devi
ce.software_version,1,3)作为十进制(2,1))> 2.0 AND clb_device.device_state> 0 GROUP BY`clb_device`.id` ORDER BY CONCAT(trim(clb_company.portal_line1),'/',trim(clb_device.name))desc LIMIT 20
看看 CONCAT(trim(clb_company.portal_line1),'/'`,trim(clb_device.name))
请提出解决方法。
在查询前使用此行:
$ this-> db-> _protect_identifiers = false;
这将停止向构建的查询中添加反引号。
Previously my all queries were running fine in CI version 2.0 but when I upgraded to 2.0.3 some of my SELECT queries were broken.
CI is adding backticks (``) automatically, but in older version its running as it is.
CI user manual have instructed to add second parameter in
db->select
as
FALSE
but still it's not working.
Code is as following:
class Company_model extends MY_Model
{
----------------
$this->db->select(' count('.$fieldname. ') as num_stations');
$this->db->select(" CONCAT_WS(',', clb_company.address1, clb_company.address2, clb_company.city, clb_company.state, clb_company.zipcode ) as companyAddress");
$this->db->from($this->_table);
$this->db->join($this->_table_device, $fieldname1. " = ". $fieldname2, 'LEFT');
$this->db->where($blablafield , '0');
----------------
The error is as follows:
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
'FROM (`clb_device`) JOIN `clb_company` ON `clb_company`.`id` = `clb_device`.`com' at line 2
SELECT `clb_device`.`id` as deviceId, `clb_pricing_specifications`.`name` as pricingSpecName, `clb_company`.`name` as companyName, `clb_device`.`mac_address` as deviceMacAddress,
`clb_device`.`reseller_model_number` as deviceModelNumber, `clb_pricing_spec_grouping`.`pricing_master_spec_id` as pricingSpecId, `clb_device`.`address` as deviceAddress,
`clb_device`.`is_home` as deviceIsHomeCharger, CONCAT(clb_company.portal_line1, `'/'`, `clb_device`.`name)` as deviceDisplayName FROM (`clb_device`) JOIN `clb_company`
ON `clb_company`.`id` = `clb_device`.`company_id` LEFT JOIN `clb_pricing_group_devices` ON `clb_device`.`id` = `clb_pricing_group_devices`.`device_id` and clb_pricing_group_devices.is_active = 1
LEFT JOIN `clb_pricing_spec_grouping` ON `clb_pricing_group_devices`.`pricing_spec_id` = `clb_pricing_spec_grouping`.`pricing_master_spec_id` LEFT JOIN `clb_pricing_specifications` ON
`clb_pricing_spec_grouping`.`pricing_spec_id` = `clb_pricing_specifications`.`id` WHERE clb_company.vendor_id is not null AND cast(substr(clb_devi
ce.software_version, 1, 3) as decimal(2,1)) > 2.0 AND clb_device.device_state > 0 GROUP BY `clb_device`.`id` ORDER BY CONCAT(trim(clb_company.portal_line1), `'/'`, trim(clb_device.name)) desc LIMIT 20
Have a look at CONCAT(trim(clb_company.portal_line1), `'/'`, trim(clb_device.name))
Please suggest the workaround.
Use this line before your query:
$this->db->_protect_identifiers=false;
This will stop adding backticks to the built query.
这篇关于CodeIgniter 2.0.3中Active Record中奇怪的反引号行为的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!