与在SQL客户端中运行相同查询相比，PHP查询返回的结果要少一个。
与在SQL客户端中运行相同查询相比，PHP查询返回的结果要少一个。
提问于 2022-03-09 01:46:40
我有一个查询来获取一些数据。当我在TablePlus (数据库客户端)的SQL编辑器中运行查询时，结果与预期的一样。
当我将查询转换为PHP项目时，每次结果都会被关闭1条记录。下面的图像是在PHP中执行查询返回的结果上的var_dump的结果。注意数组值的数量&第一个结果。
可以看到，SQL将返回9个结果，顶部的结果具有id 57115 (正确)。PHP将返回8个结果，记录中缺少id 57115 (不正确)。在我的PHP项目中，我使用的是CodeIgniter版本3.1.10。我试过使用CodeIgniter的查询生成器、常规的$this->db->query()函数&PHP的mysqli扩展。所有这三种方法都给出了相同的8行结果。这也不仅仅是8&9的结果，如果我修改查询，它总是由一个关闭。例如，我限制了按日期与行一起返回的结果
and `acd_3cx_leads`.`lastModified` >= '2022-01-01'
删除它将返回SQL中的2,449个结果，而PHP将返回2,448个结果。再减1分。我完全不知道是什么导致了这一点，这在所有4种实现中都是完全相同的查询，那么为什么PHP由1关闭呢？
以下是4个查询，供参考：
SQL
select
`acd_3cx_leads`.`id`,
`acd_3cx_leads`.`dialCount`,
@hoursSinceLastUpdate := timestampdiff(hour, `acd_3cx_leadstatus`.`createdOn`, NOW()) hoursSinceLastUpdate,
max(acd_3cx_leadstatus.createdOn) leadCountLastUpdated,
max(acd_3cx_lead_schedule_call_back_sms_log.smsType) lastSmsType
from
`acd_3cx_leads`
left join `acd_3cx_leadstatus` on `acd_3cx_leadstatus`.`leadId` = `acd_3cx_leads`.`id`
and `acd_3cx_leadstatus`.`dialCount` = `acd_3cx_leads`.`dialCount`
left join `acd_3cx_lead_schedule_call_back_sms_log` on `acd_3cx_lead_schedule_call_back_sms_log`.`leadId` = `acd_3cx_leads`.`id`
where
`acd_3cx_leads`.`doNotContact` = 0
and `acd_3cx_leads`.`active` = 1
and `acd_3cx_leads`.`closeTypeId` is null
and `acd_3cx_leads`.`lastModified` >= '2022-01-01'
group by
`acd_3cx_leads`.`id`
having (acd_3cx_leads.dialCount = 1
and @hoursSinceLastUpdate >= 5
and lastSmsType is null)
OR(acd_3cx_leads.dialCount = 1
and @hoursSinceLastUpdate >= 24
and(lastSmsType is null
or lastSmsType < 2))
OR(acd_3cx_leads.dialCount = 1
and @hoursSinceLastUpdate >= 48
and(lastSmsType is null
or lastSmsType < 3))
OR(acd_3cx_leads.dialCount = 2
and @hoursSinceLastUpdate >= 4
and lastSmsType is null)
OR(acd_3cx_leads.dialCount = 2
and @hoursSinceLastUpdate >= 24
and(lastSmsType is null
or lastSmsType < 2))
OR(acd_3cx_leads.dialCount = 2
and @hoursSinceLastUpdate >= 48
and(lastSmsType is null
or lastSmsType < 3))
OR(acd_3cx_leads.dialCount = 3
and @hoursSinceLastUpdate >= 6
and(lastSmsType is null
or lastSmsType < 4))
order by
`acd_3cx_leads`.`lastModified` desc代码生成器的查询生成器
public function getRedialLeads(): array
{
$lead = TABLE_NAMES['acd_3cx_leads'];
$leadStatus = TABLE_NAMES['acd_3cx_leadstatus'];
$smsLog = TABLE_NAMES['acd_3cx_lead_schedule_call_back_sms_log'];
$this->db->select("
{$lead}.id,
{$lead}.dialCount,
@hoursSinceLastUpdate := timestampdiff(hour, {$leadStatus}.createdOn, NOW()) hoursSinceLastUpdate,
max({$leadStatus}.createdOn) leadCountLastUpdated,
max({$smsLog}.smsType) lastSmsType
");
$this->db->join($leadStatus, "{$leadStatus}.leadId = {$lead}.id and {$leadStatus}.dialCount = {$lead}.dialCount", 'left');
$this->db->join($smsLog, "{$smsLog}.leadId = {$lead}.id", 'left');
$this->db->where("{$lead}.doNotContact", 0);
$this->db->where("{$lead}.active", 1);
$this->db->where("{$lead}.closeTypeId is null");
$this->db->where("{$lead}.lastModified >= '2022-01-01'");
$this->db->group_by("{$lead}.id");
// Dial 2 after +5 hours
$this->db->having("(
{$lead}.dialCount = 1
and @hoursSinceLastUpdate >= 5
and lastSmsType is null
)", null, false);
// Dial 2 after +24 hours
$this->db->or_having("(
{$lead}.dialCount = 1
and @hoursSinceLastUpdate >= 24
and (lastSmsType is null or lastSmsType < 2)
)", null, false);
// Dial 2 after +48 hours
$this->db->or_having("(
{$lead}.dialCount = 1
and @hoursSinceLastUpdate >= 48
and (lastSmsType is null or lastSmsType < 3)
)", null, false);
// Dial 3 after +4 hours
$this->db->or_having("(
{$lead}.dialCount = 2
and @hoursSinceLastUpdate >= 4
and lastSmsType is null
)", null, false);
// Dial 3 after +24 hours
$this->db->or_having("(
{$lead}.dialCount = 2
and @hoursSinceLastUpdate >= 24
and (lastSmsType is null or lastSmsType < 2)
)", null, false);
// Dial 3 after +48 hours
$this->db->or_having("(
{$lead}.dialCount = 2
and @hoursSinceLastUpdate >= 48
and (lastSmsType is null or lastSmsType < 3)
)", null, false);
// Dial 4 after +6 hours
$this->db->or_having("(
{$lead}.dialCount = 3
and @hoursSinceLastUpdate >= 6
and (lastSmsType is null or lastSmsType < 4)
)", null, false);
$this->db->order_by("{$lead}.lastModified desc");
return $this->db->get($lead)->result_array();
}编解码器的查询函数
public function getRedialLeads2(): array
{
$query = $this->db->query("
select
`acd_3cx_leads`.`id`,
`acd_3cx_leads`.`dialCount`,
@hoursSinceLastUpdate := timestampdiff(hour, `acd_3cx_leadstatus`.`createdOn`, NOW()) hoursSinceLastUpdate,
max(acd_3cx_leadstatus.createdOn) leadCountLastUpdated,
max(acd_3cx_lead_schedule_call_back_sms_log.smsType) lastSmsType
from
`acd_3cx_leads`
left join `acd_3cx_leadstatus` on `acd_3cx_leadstatus`.`leadId` = `acd_3cx_leads`.`id`
and `acd_3cx_leadstatus`.`dialCount` = `acd_3cx_leads`.`dialCount`
left join `acd_3cx_lead_schedule_call_back_sms_log` on `acd_3cx_lead_schedule_call_back_sms_log`.`leadId` = `acd_3cx_leads`.`id`
where
`acd_3cx_leads`.`doNotContact` = 0
and `acd_3cx_leads`.`active` = 1
and `acd_3cx_leads`.`closeTypeId` is null
and `acd_3cx_leads`.`lastModified` >= '2022-01-01'
group by
`acd_3cx_leads`.`id`
having (acd_3cx_leads.dialCount = 1
and @hoursSinceLastUpdate >= 5
and lastSmsType is null)
OR(acd_3cx_leads.dialCount = 1
and @hoursSinceLastUpdate >= 24
and(lastSmsType is null
or lastSmsType < 2))
OR(acd_3cx_leads.dialCount = 1
and @hoursSinceLastUpdate >= 48
and(lastSmsType is null
or lastSmsType < 3))
OR(acd_3cx_leads.dialCount = 2
and @hoursSinceLastUpdate >= 4
and lastSmsType is null)
OR(acd_3cx_leads.dialCount = 2
and @hoursSinceLastUpdate >= 24
and(lastSmsType is null
or lastSmsType < 2))
OR(acd_3cx_leads.dialCount = 2
and @hoursSinceLastUpdate >= 48
and(lastSmsType is null
or lastSmsType < 3))
OR(acd_3cx_leads.dialCount = 3
and @hoursSinceLastUpdate >= 6
and(lastSmsType is null
or lastSmsType < 4))
order by
`acd_3cx_leads`.`lastModified` desc
");
return $query->result_array();
}PHP的mysqli扩展
public function getRedialLeads3(): array
{
// Obviously omitted sensitive details
$mysqli = mysqli_connect('hostname', 'username', 'password', 'database');
$query = "
select
`acd_3cx_leads`.`id`,
`acd_3cx_leads`.`dialCount`,
@hoursSinceLastUpdate := timestampdiff(hour, `acd_3cx_leadstatus`.`createdOn`, NOW()) hoursSinceLastUpdate,
max(acd_3cx_leadstatus.createdOn) leadCountLastUpdated,
max(acd_3cx_lead_schedule_call_back_sms_log.smsType) lastSmsType
from
`acd_3cx_leads`
left join `acd_3cx_leadstatus` on `acd_3cx_leadstatus`.`leadId` = `acd_3cx_leads`.`id`
and `acd_3cx_leadstatus`.`dialCount` = `acd_3cx_leads`.`dialCount`
left join `acd_3cx_lead_schedule_call_back_sms_log` on `acd_3cx_lead_schedule_call_back_sms_log`.`leadId` = `acd_3cx_leads`.`id`
where
`acd_3cx_leads`.`doNotContact` = 0
and `acd_3cx_leads`.`active` = 1
and `acd_3cx_leads`.`closeTypeId` is null
and `acd_3cx_leads`.`lastModified` >= '2022-01-01'
group by
`acd_3cx_leads`.`id`
having (acd_3cx_leads.dialCount = 1
and @hoursSinceLastUpdate >= 5
and lastSmsType is null)
OR(acd_3cx_leads.dialCount = 1
and @hoursSinceLastUpdate >= 24
and(lastSmsType is null
or lastSmsType < 2))
OR(acd_3cx_leads.dialCount = 1
and @hoursSinceLastUpdate >= 48
and(lastSmsType is null
or lastSmsType < 3))
OR(acd_3cx_leads.dialCount = 2
and @hoursSinceLastUpdate >= 4
and lastSmsType is null)
OR(acd_3cx_leads.dialCount = 2
and @hoursSinceLastUpdate >= 24
and(lastSmsType is null
or lastSmsType < 2))
OR(acd_3cx_leads.dialCount = 2
and @hoursSinceLastUpdate >= 48
and(lastSmsType is null
or lastSmsType < 3))
OR(acd_3cx_leads.dialCount = 3
and @hoursSinceLastUpdate >= 6
and(lastSmsType is null
or lastSmsType < 4))
order by
`acd_3cx_leads`.`lastModified` desc
";
$result = mysqli_query($mysqli, $query, MYSQLI_USE_RESULT);
return mysqli_fetch_all($result, MYSQLI_ASSOC);
}回答 1
Stack Overflow用户
回答已采纳
发布于 2022-03-09 02:09:33
根据@mickmackusa的建议，我从SELECT & HAVING子句中删除了SQL变量HAVING。这解决了我的问题。所以SELECT线
@hoursSinceLastUpdate := timestampdiff(hour, `acd_3cx_leadstatus`.`createdOn`, NOW()) hoursSinceLastUpdate,是现在
timestampdiff(hour, `acd_3cx_leadstatus`.`createdOn`, NOW()) hoursSinceLastUpdate,在HAVING中，所有出现的
and @hoursSinceLastUpdate >= x现在
and hoursSinceLastUpdate >= x因此，现在SQL & PHP返回的记录之间存在奇偶性。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/71403428
复制相关文章
相似问题
腾讯云开发者
