I have a query to get some data. When I run the query in the SQL editor of TablePlus (a database client), the results are as expected.
When I convert the query into my PHP project the results are off by 1 record every time. The following image is the result of a var_dump
on the results returned by executing the query in PHP. Note the number of array values & the first result.
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);
}
CodePudding user response:
Following advice from @mickmackusa, I removed the SQL variable hoursSinceLastUpdate
from the SELECT
& HAVING
clauses. This fixed my issue. So the SELECT
line
@hoursSinceLastUpdate := timestampdiff(hour, `acd_3cx_leadstatus`.`createdOn`, NOW()) hoursSinceLastUpdate,
is now
timestampdiff(hour, `acd_3cx_leadstatus`.`createdOn`, NOW()) hoursSinceLastUpdate,
In the HAVING
, all occurrences of
and @hoursSinceLastUpdate >= x
are now
and hoursSinceLastUpdate >= x
So there is now parity between the records return from SQL & PHP.