Search code examples
phpmysqlcodeigniter

PHP query returning exactly 1 less result compared to running the same query in a SQL client

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.

SQL results

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.

PHP results

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);
    }
Solution

  • 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.