CODEWITHSUNDEEP
codeigniter
Bala
Bala

Reputation: 3

payment and receipt records display in single table using codeigniter and mysql

Model:

$this->db->where('payment_details.created_on >= ',$start_date);
    $this->db->where('payment_details.created_on <= ',$end_date);
    $this->db->where('receipt_details.created_on >= ',$start_date);
    $this->db->where('receipt_details.created_on <= ',$end_date);        
    $this->db->select('payment_details.id,payment_details.payment_no,payment_details.site_id,payment_details.vendor_id,payment_details.work,payment_details.description,payment_details.date,payment_details.amount,payment_details.tax_type,payment_details.tax,payment_details.subtotal,payment_details.grand_total,payment_details.created_on,receipt_details.id,receipt_details.receipt_no,receipt_details.site_id,receipt_details.vendor_id,receipt_details.work,receipt_details.description,receipt_details.date,receipt_details.amount,receipt_details.tax_type,receipt_details.tax,receipt_details.subtotal,receipt_details.grand_total,receipt_details.created_on,vendor.id,vendor.vname,site.id,site.sname');

$this->db->from('payment_details');        
$this->db->join('vendor','vendor.id=payment_details.vendor_id OR vendor.id=receipt_details.vendor_id','LEFT');
$this->db->join('site','site.id=payment_details.site_id OR site.id=receipt_details.site_id','LEFT');
return $this->db->get()->result();

enter image description here

I will try but there is error occur while running this query Error: Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LEFT JOIN vendor ON vendor.id=payment_details.vendor_id OR vendor.`i' at line 2

SELECT payment_details.id, payment_details.payment_no, payment_details.site_id, payment_details.vendor_id, payment_details.work, payment_details.description, payment_details.date, payment_details.amount, payment_details.tax_type, payment_details.tax, payment_details.subtotal, payment_details.grand_total, payment_details.created_on, receipt_details.id, receipt_details.receipt_no, receipt_details.site_id, receipt_details.vendor_id, receipt_details.work, receipt_details.description, receipt_details.date, receipt_details.amount, receipt_details.tax_type, receipt_details.tax, receipt_details.subtotal, receipt_details.grand_total, receipt_details.created_on, vendor.id, vendor.vname, site.id, site.sname LEFT JOIN vendor ON vendor.id=payment_details.vendor_id OR vendor.id=receipt_details.vendor_id LEFT JOIN site ON site.id=payment_details.site_id OR site.id=receipt_details.site_id WHERE payment_details.created_on >= '2018-05-01' AND payment_details.created_on <= '2018-05-31' AND receipt_details.created_on >= '2018-05-01' AND receipt_details.created_on <= '2018-05-31'

Filename: D:/xampp/htdocs/construct/system/database/DB_driver.php

Line Number: 691 Thanks in advance......

Upvotes: 0

Views: 597

Answers (1)

Pradeep
Pradeep

Reputation: 9717

Hope this will help you : (not tested)

your query structure should be like this, do necessary changes providing you just hint

$sql = 'SELECT payment_details.id,payment_details.payment_no,payment_details.site_id,payment_details.vendor_id,payment_details.work,payment_details.description,payment_details.date,payment_details.amount,payment_details.tax_type,payment_details.tax,payment_details.subtotal,payment_details.grand_total,payment_details.created_on,receipt_details.id,receipt_details.receipt_no,receipt_details.site_id,receipt_details.vendor_id,receipt_details.work,receipt_details.description,receipt_details.date,receipt_details.amount,receipt_details.tax_type,receipt_details.tax,receipt_details.subtotal,receipt_details.grand_total,receipt_details.created_on,vendor.id,vendor.vname,site.id,site.sname 
        FROM payment_details  
        JOIN (vendor, receipt_details, site)
        ON (vendor.id=payment_details.vendor_id OR vendor.id=receipt_details.vendor_id
        AND site.id=payment_details.site_id OR site.id=receipt_details.site_id)';
$query = $this->db->query($sql);
return $query->result();

Upvotes: 0

Related Questions