下面是sql查询
SELECT * FROM seedbed
LEFT OUTER JOIN fertilizers AS seedfert ON seedfert.fert_protocol =seedbed.seed_protocol
LEFT OUTER JOIN pesticides AS seedpest ON seedpest.pest_protocol = seedbed.seed_protocol
LEFT OUTER JOIN fertilizers_list AS seedfert_list ON seedfert.fert_id =seedfert_list.id
LEFT OUTER JOIN pesticides_list AS seedpest_list ON seedpest.pest_id = seedpest_list.id
LEFT OUTER JOIN field ON field.field_protocol = seedbed.seed_protocol + "%"
LEFT OUTER JOIN fertilizers AS fieldfert ON fieldfert.fert_protocol = field.field_protocol
LEFT OUTER JOIN pesticides AS fieldpest ON fieldpest.pest_protocol = field.field_protocol
LEFT OUTER JOIN stock ON stock.field_protocol = field.field_protocol
LEFT OUTER JOIN fertilizers_list AS fieldfert_list ON fieldfert.fert_id =fieldfert_list.id
LEFT OUTER JOIN pesticides_list AS fieldpest_list ON fieldpest.pest_id = fieldpest_list.id
WHERE seedbed.seed_protocol LIKE "1/2013/2%"
ORDER BY field_num;
在php是完全相同的，我使用codeigniter，但我不使用这个活动记录库，所以几乎是相同的。
$sql = "SELECT * FROM seedbed
LEFT OUTER JOIN fertilizers AS seedfert ON seedfert.fert_protocol = seedbed.seed_protocol
LEFT OUTER JOIN pesticides AS seedpest ON seedpest.pest_protocol = seedbed.seed_protocol
LEFT OUTER JOIN fertilizers_list AS seedfert_list ON seedfert.fert_id = seedfert_list.id
LEFT OUTER JOIN pesticides_list AS seedpest_list ON seedpest.pest_id = seedpest_list.id
LEFT OUTER JOIN field ON field.field_protocol = seedbed.seed_protocol + '%'
LEFT OUTER JOIN fertilizers AS fieldfert ON fieldfert.fert_protocol = field.field_protocol
LEFT OUTER JOIN pesticides AS fieldpest ON fieldpest.pest_protocol = field.field_protocol
LEFT OUTER JOIN stock ON stock.field_protocol = field.field_protocol
LEFT OUTER JOIN fertilizers_list AS fieldfert_list ON fieldfert.fert_id =fieldfert_list.id
LEFT OUTER JOIN pesticides_list AS fieldpest_list ON fieldpest.pest_id = fieldpest_list.id
WHERE seedbed.seed_protocol LIKE '".$this->db->escape_like_str($ins_protocol)."%'
ORDER BY field_num";
$query = $this->db->query($sql);
$data = $query->result();
print_r($data);
现在的问题是，在sql server我得到所有的结果，我应该得到，虽然在php我不得到我的前两个join的结果。我不知道为什么?
我希望有人知道发生了什么。
正如OP所暗示的那样，问题很可能与多个表中具有相同的字段名有关，因此它们在执行时将被组合/覆盖，并在PHP中返回。
解决方案是为所有具有冲突名称的字段/列提供唯一别名。不幸的是，我想不出一个自动完成这项工作的方法，所以我认为这必须是一个手动的过程。
表如何分组也可能有问题，所以以防万一，我将扔进一堆括号!
SELECT seedbed.*,
seedfert.field1 AS seedfert_field1, seedfert.field2 AS seedfert_field2,
seedpest.field1 AS seedpest_field1, seedpest.field2 AS seedpest_field2,
seedfert_list.field1 AS seedfert_list_field1, seedfert_list.field2 AS seedfert_list_field2,
seedpest_list.field1 AS seedpest_list_field1, seedpest_list.field2 AS seedpest_list_field2,
etc...
FROM (((((((((seedbed
LEFT OUTER JOIN fertilizers AS seedfert ON seedfert.fert_protocol = seedbed.seed_protocol)
LEFT OUTER JOIN pesticides AS seedpest ON seedpest.pest_protocol = seedbed.seed_protocol)
LEFT OUTER JOIN fertilizers_list AS seedfert_list ON seedfert.fert_id = seedfert_list.id)
LEFT OUTER JOIN pesticides_list AS seedpest_list ON seedpest.pest_id = seedpest_list.id)
LEFT OUTER JOIN field ON field.field_protocol = seedbed.seed_protocol + '%')
LEFT OUTER JOIN fertilizers AS fieldfert ON fieldfert.fert_protocol = field.field_protocol)
LEFT OUTER JOIN pesticides AS fieldpest ON fieldpest.pest_protocol = field.field_protocol)
LEFT OUTER JOIN stock ON stock.field_protocol = field.field_protocol)
LEFT OUTER JOIN fertilizers_list AS fieldfert_list ON fieldfert.fert_id =fieldfert_list.id)
LEFT OUTER JOIN pesticides_list AS fieldpest_list ON fieldpest.pest_id = fieldpest_list.id
WHERE seedbed.seed_protocol LIKE '".$this->db->escape_like_str($ins_protocol)."%'
ORDER BY field_num
在Codeigniter中运行:
echo $this->db->last_query();
和非ci查询
检查整个数据库中是否存在可能导致连接出现问题的null。其他人也遇到过由于NULL处理而导致PHP返回奇怪结果的问题。
this works on me:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_WARNINGS ON
SET ANSI_PADDING ON