MySql query working in PHPmyadmin, but not working in codeigniter

Question

i am working in codeigniter project when i try to get datas from database using 5 tables in a single query.

In phpmyadmin sql query works fine but codeigniter query not working.

My Phpmyadmin Query :

select sma_sales.date, sma_sales.reference_no, biller, sma_companies.name as DeliveryRep,customer,c.code, 
GROUP_CONCAT(CONCAT(sma_sale_items.product_name, ' (', sma_sale_items.quantity, ')') SEPARATOR '\n') as iname, 
grand_total, paid, sma_payments.amount as pyname, 
payment_status
from sma_sales
left outer join sma_sale_items on sma_sale_items.sale_id=sma_sales.id
left outer join sma_companies on sma_companies.id=sma_sales.delivered_id
left outer join sma_companies as c on c.id=sma_sales.customer_id
left outer join sma_warehouses on sma_warehouses.id=sma_sales.warehouse_id
left outer join sma_payments on sma_payments.sale_id=sma_sales.id
group by sma_sales.id

My Codeigniter query:

->select("date, reference_no, biller, companies.name as DeliveryRep,customer,c.code, "
                        . "GROUP_CONCAT(CONCAT(" . $this->db->dbprefix('sale_items') . ".product_name,"
                        . " ' (', " . $this->db->dbprefix('sale_items') . ".quantity, ')') SEPARATOR '\n') as iname,"
                        . " grand_total, paid,payments.amount as pyname, payment_status", FALSE)
                ->from('sales')
                ->join('sale_items', 'sale_items.sale_id=sales.id', 'left')
                ->join('companies', 'companies.id=sales.delivered_id', 'left')
                ->join('companies as c', 'c.id=sales.customer_id', 'left')
                ->join('warehouses', 'warehouses.id=sales.warehouse_id', 'left')
                ->join('payments', 'payments.sale_id=sales.id', 'left')
                ->group_by('sales.id')
$q = $this->db->get();

sma nothing but database prefix

Touheed Khan · Accepted Answer · 2018-02-09 10:33:13Z

3

You need to add sma in table name, it is part of table name.

Query:

$this->db->select("date, reference_no, biller, sma_companies.name as DeliveryRep,customer,c.code, "
                            . "GROUP_CONCAT(CONCAT(" . $this->db->dbprefix('sale_items') . ".product_name,"
                            . " ' (', " . $this->db->dbprefix('sale_items') . ".quantity, ')') SEPARATOR '\n') as iname,"
                            . " grand_total, paid,sma_payments.amount as pyname, payment_status", FALSE)
                    ->from('sma_sales')
                    ->join('sma_sale_items', 'sma_sale_items.sale_id=sma_sales.id', 'left')
                    ->join('sma_companies', 'sma_companies.id=sma_sales.delivered_id', 'left')
                    ->join('sma_companies as c', 'c.id=sma_sales.customer_id', 'left')
                    ->join('sma_warehouses', 'sma_warehouses.id=sma_sales.warehouse_id', 'left')
                    ->join('sma_payments', 'sma_payments.sale_id=sales.id', 'left')
                    ->group_by('sales.id')
    $q = $this->db->get();

You can debug you query by search and set this as true,

$db['default']['db_debug'] = true;

in your config/database.php

Conclusion : Issue was there in alias of table names, after fixing alias name everything worked fine.

edited Feb 9, 2018 at 10:33

answered Feb 9, 2018 at 9:44

Touheed Khan

2,14918 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Magnil Amar Over a year ago

we don't need to mention that sma as prefix. my project library functions automatically map the prefix with table name

Magnil Amar Over a year ago

can not see the error because this query running for pdf download.

Magnil Amar Over a year ago

Once click download page display full empty

Touheed Khan Over a year ago

put exit or die after get() and echo $this->db->last_query();

Touheed Khan Over a year ago

search and set this as true, $db['default']['db_debug'] = true; in your config/database.php, then check what error you are getting.

|

Gopal Palraj · Accepted Answer · 2018-02-09 10:56:04Z

You are using 5 tables.may be same column name can exist in multiple tables.you ca use alias for all tables and change you query be like:

$this->db->select("s.date , s.reference_no, s.biller, companies.name as DeliveryRep,s.customer,c.code, "
                       . "GROUP_CONCAT(CONCAT(" . $this->db->dbprefix('sale_items') . ".product_name, "
                       . "' (', " . $this->db->dbprefix('sale_items') . ".quantity, ')') SEPARATOR '\n') as iname, "
                       . "s.grand_total, s.paid,pay.amount as pyname, s.payment_status", FALSE)
               ->from('sales as s')
               ->join('sale_items', 'sale_items.sale_id=s.id', 'left')
               ->join('companies', 'companies.id=s.delivered_id', 'left')
               ->join('companies as c', 'c.id=s.customer_id', 'left')
               ->join('warehouses', 'warehouses.id=s.warehouse_id', 'left')
               ->join('payments as pay', 'pay.sale_id=s.id', 'left')
               ->group_by('s.id')
               ->order_by('s.date desc');

Collectives™ on Stack Overflow

MySql query working in PHPmyadmin, but not working in codeigniter

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related