php - Jquery Ajax 在无限制地从 Mysql 中拉取时不返回任何内容，也没有错误

Question

我有一个 Jquery 函数，它使用 Ajax 从 Mysql 表中提取行。将限制设置为 100 行效果很好，但一旦我不对行数设置限制，它就不会返回任何内容。

关于调试这个有什么想法吗？

Jquery:

function showAll(table) {
            document.getElementById(table + '_div').innerHTML = '';
                showLoad(table);
                getCount(table);
                $.ajax({
                    type: "GET",
                    url: '<?php echo EWConfig::$URL;?>/ExpressWay/Workplans/populateBuckets/showAll/<?php echo $department; ?>/' + table + '/' + $('select[name="data[employee]"]').val() + '/' + $('select[name="data[store]"]').val(),
                    dataType: "html",
                    success: function (res) {
                        document.getElementById(table + '_div').innerHTML = res;                             
                    },
                    error: function(jqXHR, textStatus, errorThrown) {
                        alert(textStatus); 
                    }
                });

        }

PHP

if($showAll === true) {
            $limitQuery = "0,100000";
        }else{
            $limitQuery = "$pageStart,$page";
        }

        //unset($bucket['fields'][0]);
        $this->set('columnHeadings', array_keys($bucket['fields']));

        $result = $this->Customer->find('all',array(
                'conditions' => $bucket['conditions'],
                'fields' => $bucket['fields'],
                'order' => $bucket['order'],
                'group' => ((isset($bucket['group'])) ? $bucket['group'] : null),
                'limit' => $limitQuery,
                'contain' => array(
                    'CustomerPersonalInformation',
                    'CustomerMarketingOption',
                    'CustomerContactInformation',
                    'LastContact',
                ),
                'joins' => $bucket['joins']
            )
        );

MySQL

SELECT DISTINCT `Customer`.`customer_id`, `Customer`.`store`, CONCAT_WS(' ',CustomerPersonalInformation.first_name,CustomerPersonalInformation.last_name) AS full_name, `CustomerContactInformation`.`primary_phone`, `CustomerContactInformation`.`email`, `CustomerMarketingOption`.`status`, `CustomerMarketingOption`.`referrer`, `CustomerInteraction`.`notes`, `Customer`.`customer_id`, `CustomerInteraction`.`created_by`, `CustomerInteraction`.`created_on`, `Customer`.`notes` FROM `expreta2_x12`.`customers` AS `Customer` LEFT JOIN `expreta2_x12`.`customer_interactions` AS `CustomerInteraction` ON (`Customer`.`customer_id` = `CustomerInteraction`.`customer_id`) LEFT JOIN `expreta2_x12`.`employees` AS `Employee` ON (`Customer`.`bdr_associate` = `Employee`.`employee_id`) LEFT JOIN `expreta2_x12`.`customers_contact_information` AS `CustomerContact` ON (`CustomerContact`.`customer_id` = `Customer`.`customer_id`) LEFT JOIN `expreta2_x12`.`customers_personal_information` AS `CustomerPersonalInformation` ON (`CustomerPersonalInformation`.`customer_id` = `Customer`.`customer_id`) LEFT JOIN `expreta2_x12`.`customers_contact_information` AS `CustomerContactInformation` ON (`CustomerContactInformation`.`customer_id` = `Customer`.`customer_id`) LEFT JOIN `expreta2_x12`.`customers_marketing_options` AS `CustomerMarketingOption` ON (`CustomerMarketingOption`.`customer_id` = `Customer`.`customer_id`) LEFT JOIN `expreta2_x12`.`customer_interactions` AS `LastContact` ON (`LastContact`.`customer_id` = `Customer`.`customer_id` AND `LastContact`.`type` IN ('answered-discussion','discussion'))  WHERE `Customer`.`bdr_associate` IS NOT NULL AND `CustomerMarketingOption`.`lead_category` = 'reference' AND `CustomerMarketingOption`.`status` NOT IN ('delivered', 'dead', 'invalid') AND `Customer`.`created_on` > '2013-04-23 00:00:00' AND `Customer`.`created_on` < DATE_SUB(CURDATE(), INTERVAL 10 DAY)  GROUP BY `Customer`.`customer_id`  ORDER BY CASE
                        WHEN `CustomerMarketingOption`.`status` = 'intensive' THEN 1
                        WHEN `CustomerMarketingOption`.`status` = 'critical' THEN 2
                        WHEN `CustomerMarketingOption`.`status` = 'hot' THEN 3
                        WHEN `CustomerMarketingOption`.`status` = 'warm' THEN 4
                        ELSE 5
                     END ASC, `Customer`.`created_on` ASC, `CustomerInteraction`.`created_on` DESC, `LastContact`.`created_on` DESC  LIMIT 0,1000000

Answer 1

您需要设置limit和offset

    $limit = 100000;
    $offset = 0;
    if ($showAll !== true) {
        $limit = $pageStart;
        $offset = $page;
    }

    //unset($bucket['fields'][0]);
    $this->set('columnHeadings', array_keys($bucket['fields']));

    $result = $this->Customer->find('all',array(
            'conditions' => $bucket['conditions'],
            'fields' => $bucket['fields'],
            'order' => $bucket['order'],
            'group' => ((isset($bucket['group'])) ? $bucket['group'] : null),
            'limit' => $limit,
            'offset' => $offset,
            'contain' => array(
                'CustomerPersonalInformation',
                'CustomerMarketingOption',
                'CustomerContactInformation',
                'LastContact',
            ),
            'joins' => $bucket['joins']
        )
    );

如果页面是实际页面，则 $offset 应该类似于 $page * $numberPerPage