Postingan lainnya

Tidak Bisa Join Datatables saat export data ke Excel di PHP CodeIgniter

Dibawah ini kodingan controller saya yang berfungsi export data ke dalam bentuk excel

if ($xls)
            {
            $this->db
                ->select("{$this->db->dbprefix('purchases')}.id as id, 
            DATE_FORMAT(date, '%Y-%m-%d %T') as date, 
            reference_no,
            {$this->db->dbprefix('users')}.first_name as cname,
            open,
            closing,
            loading_date, 
            CONCAT(CONCAT({$this->db->dbprefix('purchases')}.supplier,' '),
            CONCAT({$this->db->dbprefix('purchases')}.branch,'')) as supplier,
            {$this->db->dbprefix('warehouses')}.code as wh,
            {$this->db->dbprefix('companies')}.vat_no as npwp,
            {$this->db->dbprefix('purchase_items')}.product_name as product_name,
            due_date,
            return_id,
            grand_total")
            ->from('purchases')
            ->join('companies', 'companies.id=purchases.supplier_id', 'left')
            ->join('warehouses', 'warehouses.id=purchases.warehouse_id', 'left')
            ->join('purchase_items', 'purchase_items.purchase_id=purchases.id', 'left')
            ->join('users', 'purchases.created_by=users.id', 'left')
            ->where('status !=', 'ro')
            ->where('status !=', 'received')
            ->order_by('purchases.reference_no', 'asc');

            if ($user) {
                $this->db->where('purchases.created_by', $user);
            }
            if ($po_number) {
                $this->db->like('purchases.reference_no', $po_number, 'both');
            }
            if ($warehouse) {
                $this->db->where('purchases.warehouse_id', $warehouse);
            }
            if ($start_date) {
                $this->db->where($this->db->dbprefix('purchases') . '.date BETWEEN "' . $start_date . '" and "' . $end_date . '"');
            }

semuanya berfungsi baik, tapi pada saat saya mau manggil "product_name" tidak bisa terpanggil. kolom "product_name" ada di table "purchase_items", tapi Ketika saya mau join table "purchases" dengan "purchase_items" tidak bisa

asumsi saya permasalahannya ada di "{$this->db->dbprefix('purchase_items')}.product_name as product_name," dan juga join "->join('purchase_items', 'purchase_items.purchase_id=purchases.id', 'left')" cuma saya sudah gonta ganti method joinnya masih tetap tidak mengambil data "product_name" dari table "purchase_items"

berikut detail database "purchases"

CREATE TABLE `sma_purchases` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `reference_no` varchar(55) NOT NULL,
  `date` timestamp NOT NULL DEFAULT current_timestamp(),
  `supplier_id` int(11) NOT NULL,
  `supplier` varchar(100) NOT NULL,
  `branch` varchar(250) NOT NULL,
  `warehouse_id` int(11) NOT NULL,
  `note` varchar(10000) NOT NULL,
  `total` decimal(25,4) DEFAULT NULL,
  `product_discount` decimal(25,4) DEFAULT NULL,

berikut detail database "purchase_items"

CREATE TABLE `sma_purchase_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `purchase_id` int(11) DEFAULT NULL,
  `transfer_id` int(11) DEFAULT NULL,
  `purchase_product_id` int(11) DEFAULT 0,
  `product_id` int(11) NOT NULL,
  `product_code` varchar(50) NOT NULL,
  `product_barcode_product` varchar(50) NOT NULL,
  `product_barcode_carton` varchar(50) NOT NULL,
  `product_name` varchar(255) NOT NULL,
  `product_second_name` varchar(255) NOT NULL,
  `product_m3` decimal(25,2) DEFAULT NULL,
  `product_totalm3` decimal(25,2) DEFAULT NULL,

tolong bantuannya letak kesalahannya dimana atau methodnya harus seperti apa? terima kasih

php codeigniter database

@leoniisabell23

2 Kontribusi 0 Poin

Diperbarui 2 minggu yang lalu

1 Jawaban:

kemungkinan pertama, mungkin Tabel purchase_items memiliki beberapa baris untuk setiap pembelian (purchases),,,, Jika setiap pembelian memiliki lebih dari satu produk, mungkin mengagregasikan product_name menjadi satu string yang dipisahkan koma,,,

$this->db
    ->select("{$this->db->dbprefix('purchases')}.id as id, 
              DATE_FORMAT(date, '%Y-%m-%d %T') as date, 
              reference_no,
              {$this->db->dbprefix('users')}.first_name as cname,
              open,
              closing,
              loading_date, 
              CONCAT(CONCAT({$this->db->dbprefix('purchases')}.supplier,' '),
              CONCAT({$this->db->dbprefix('purchases')}.branch,'')) as supplier,
              {$this->db->dbprefix('warehouses')}.code as wh,
              {$this->db->dbprefix('companies')}.vat_no as npwp,
              GROUP_CONCAT({$this->db->dbprefix('purchase_items')}.product_name SEPARATOR ', ') as product_name, 
              due_date,
              return_id,
              grand_total")
    ->from('purchases')
    ->join('companies', 'companies.id=purchases.supplier_id', 'left')
    ->join('warehouses', 'warehouses.id=purchases.warehouse_id', 'left')
    ->join('purchase_items', 'purchase_items.purchase_id=purchases.id', 'left')
    ->join('users', 'purchases.created_by=users.id', 'left')
    ->where('status !=', 'ro')
    ->where('status !=', 'received')
    ->group_by('purchases.id') // Tambahkan ini untuk menghindari duplikasi baris
    ->order_by('purchases.reference_no', 'asc');

nah,, kemungkinan ke-2,, mungkin join type nya, entah itu LEFT JOIN, INNER JOIN apa lah itu (sampai sekarang aku masi belom ngerti bener2 tentang itu) semoga membantu

@yukaristel

35 Kontribusi 31 Poin

Dipost 2 minggu yang lalu

Tanggapan

betul di "purchase_items" ada beberapa baris untuk setiap pembelian/purchases, cuma saya juga sudah coba di group concat dan juga concat hasilnya tetap tidak bisa.

@leoniisabell23

SELECT p.id, p.reference_no, pi.product_name FROM sma_purchases p LEFT JOIN sma_purchase_items pi ON pi.purchase_id = p.id WHERE p.id = [coba isi dengan id pembeliannya];

coba sql ini di db bisa ga

@yukaristel