php - LEFT JOIN 未输出所需结果

Question

我这里有使用 codeigniter 的示例查询。我用它来获取满足我的 where 条件的数据。

型号:

    function quarterly1_sales($year_date, $q1_start){
     $where = array(
     'status'   => 'purchased',
     'payment.paymentdate >=' => $q1_start,
     'payment.paymentdate <=' => 'DATE_ADD('.$q1_start.',INTERVAL 1 QUARTER)');
    return $this->db
    ->select('COUNT(payment.keyid) AS rec_count')
    ->select('product_key.client_name, product_key.contact_email, product_key.status, product_key.id, payment.paymentdate, (payment.id) as pid,payment.subscription_type')
    ->from('product_key')
    ->where($where)
    ->join('payment', 'payment.keyid=product_key.id', 'left outer')
    ->group_by('product_key.id')
    ->get()
    ->result(); 
    }

Controller :

if($data['filter'] == 'quarterly'){
//1st quarter of the year
    @$q1_start = date('Y-m-d', strtotime(date($year_date).'-01-01'));
$data['quarterly1_sales'] = $this->basemodel->quarterly1_sales($year_date, $q1_start);
$this->load->view('sales', $data);          
}

查看:

if($filter == 'quarterly'){
    $title = "Quarterly Sales Summary for ".$date_year;

    echo "<br /><strong>".$title."</strong></div>
        </tr>"<tr>
    <th>Name</th>
    <th>Email Address</th>
    <th>Payment Date</th>
    <th>Subtotal Amount</th>

";

    foreach ($quarterly1_sales as $quarter1) :

        echo $client_name = $quarter1->client_name;
        echo $paymentdate = $quarter1->paymentdate;
        echo $email = $quarter1->contact_email;
        echo $recount= $quarter1->rec_count;
        echo $sum_amount = $recount * 588;
    if($recount == 0){
        $display = "display:none;";
    }else{
        $display = "";
    }

结束；

我的问题是，由于我的数据从 2012 年和 2013 年开始，所以不应该有任何结果，但它输出了所有内容。希望有人能在这方面帮助我。谢谢。

Answer 1

Codeigniter 的事件记录类会自动转义传递给 where() 函数的所有内容。因此，您对 MySQL 函数 DATE_ADD() 的调用将作为字符串传入，并由 MySQL 按字面意思处理，而不是进行计算。要解决此问题，请将该 where 子句移至单独的函数调用，并将第三个参数设置为 FALSE，从而关闭转义。

$where = array(
     'status'   => 'purchased',
     'payment.paymentdate >=' => $q1_start);
    return $this->db
    ->select('COUNT(payment.keyid) AS rec_count')
    ->select('product_key.client_name, product_key.contact_email, product_key.status, product_key.id, payment.paymentdate, (payment.id) as pid,payment.subscription_type')
    ->from('product_key')
    ->where($where)
    ->where('payment.paymentdate <=', 'DATE_ADD('.$this->db->escape($q1_start).',INTERVAL 1 QUARTER)', FALSE)
    ->join('payment', 'payment.keyid=product_key.id', 'left outer')
    ->group_by('product_key.id')
    ->get()
    ->result();