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
The above query can run and give me the required result as i want but in phpmyadmin only
the question is this that when i put this query in my php code (codeigniter) and the model generated query is not running i don't know what to do please help me
PHP code
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();}
This is php generated Query
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
When i run the php generated query it didn't give me the required result as the first query mentioned
Error is given below
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM (
invoice
) JOINstudent
ONinvoice
.studentID
=student
.studentID
' at line 2