codeigniter 与在SQL客户端中运行相同的查询相比，PHP查询返回的结果正好少1个

wfypjpf4  于 2022-12-07  发布在  PHP
关注(0)|答案(1)|浏览(88)

我有一个查询来获取一些数据。当我在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个结果，如果我修改查询，它总是相差一。例如，我用下面的行限制按日期返回的结果
andacd_3cx_leads.lastModified>= '2022-01-01'
删除这个将返回2，449个结果在SQL中，而PHP将返回2，448个结果。再次相差1。我完全不知道是什么原因造成的，这是完全相同的查询在所有4个实现，那么为什么PHP相差1？
以下是4个查询以供参考：

查询语句

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的查询生成器

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的查询函数

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

1条答案

按热度按时间
aiqt4smr

aiqt4smr1#

根据@mickmackusa的建议，我从SELECT和HAVING子句中删除了SQL变量hoursSinceLastUpdate。

@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返回的记录之间存在奇偶校验。

赞(0）分享  回复(0）举报  2022-12-07
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com