我有一個查詢來獲取一些資料。當我在 TablePlus（資料庫客戶端）的 SQL 編輯器中運行查詢時，結果符合預期。
當我將查詢轉換為我的 PHP 專案時，結果每次都會減少 1 條記錄。下圖是var_dump在 PHP 中執行查詢回傳的結果的結果。注意陣列值的數量和第一個結果。
One can see the SQL will return 9 results, with the top result having id 57115 (correct). PHP will return 8 results with the record of id 57115 missing (incorrect). I'm using CodeIgniter version 3.1.10 for my PHP project. I've tried using CodeIgniter's query builder, the regular $this->db->query() function, & PHP's mysqli extension. All three of these approaches give the same result of 8 rows. It's not just 8 & 9 results either, if I modify the query it's always off by one. For example, I'm limiting the results that are returned by date with the line
and `acd_3cx_leads`.`lastModified` >= '2022-01-01'
Removing this will return 2,449 results in SQL whereas PHP will return 2,448 results. Off by 1 again. I'm absolutely lost on what is causing this, it's the exact same query across all 4 implementations, so why is PHP off by one?
For reference, here is the 4 queries:
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
CodeIgniter's query builder
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();
}
CodeIgniter's query function
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's mysqli extension
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);
}
uj5u.com熱心網友回復：
根據@mickmackusa 的建議，我hoursSinceLastUpdate從SELECT&HAVING子句中洗掉了 SQL 變數。這解決了我的問題。所以這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 回傳的記錄之間存在奇偶性。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/445968.html
標籤：php mysql codeigniter