php - php在phpmyadmin中生成查询显示错误

Question

select student.studentID, invoice.invoiceID, student.name,  
MONTHNAME(STR_TO_DATE(invoice.month, '%m')) as Month, invoice.tuitionfee as 'Total Amount', payment.tuitionpaid
from invoice 
join student
left join payment
on invoice.studentID = student.studentID and invoice.invoiceID = payment.invoiceID
where invoice.`create_date` > DATE_SUB(now(), INTERVAL 6 MONTH) and student.studentID = '28' group by invoice.month

上面的查询可以运行并给我所需的结果，但仅限于 phpmyadmin

问题是，当我将此查询放入我的 php 代码（codeigniter）并且模型生成的查询未运行时，我不知道该怎么做，请帮助我

PHP 代码

public function get_month_wise_dues($id){
    $this->db->select("student.studentID, 
    invoice.invoiceID, student.name, 
    MONTHNAME(STR_TO_DATE(invoice.month, '%m')) as Month, 
    invoice.tuitionfee as 'Total Amount', payment.tuitionpaid");
    $this->db->from('invoice');
    $this->db->join('student','invoice.studentID = student.studentID');
    $this->db->join('payment','invoice.invoiceID = payment.invoiceID','LEFT');
    $this->db->where('invoice.`create_date` > DATE_SUB(now(), INTERVAL 6 MONTH) and student.studentID=', $id);
    $this->db->group_by('invoice.month');
    $this->db->order_by('invoice.month', 'asc'); 
    $query = $this->db->get();
    // echo $this->db->last_query();
    // exit;
    return $query->result();}

这是 php 生成的查询

SELECT `student`.`studentID`, `invoice`.`invoiceID`, `student`.`name`, MONTHNAME(STR_TO_DATE(invoice.month, `'%m'))` as Month, `invoice`.`tuitionfee` as 'Total Amount', `payment`.`tuitionpaid`
FROM (`invoice`)
JOIN `student` ON `invoice`.`studentID` = `student`.`studentID`
LEFT JOIN `payment` ON `invoice`.`invoiceID` = `payment`.`invoiceID`
WHERE `invoice`.`create_date` > DATE_SUB(now(), INTERVAL 6 MONTH) and 
student.studentID= '28'
GROUP BY `invoice`.`month`
ORDER BY `invoice`.`month` asc

当我运行 php 生成的查询时，它没有像提到的第一个查询那样给我所需的结果

错误如下

1064 - 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在 'FROM ( invoice) JOIN studentON附近使用的正确语法invoice。studentID= student。studentID' 在第 2 行

标签： phpmysqlcodeigniter