Mysql query sum query in codeigniter

Question

the above link is one of my question. I got the answer for the above question. But now I have a sql query as follows,

SELECT SUM(IF(pay_type = 1, `pay_amount`, `0))` mess_pay, SUM(IF(pay_type=2, `pay_amount`, `0))` est_pay FROM (`mess_stock`);

I want to use above code in codeigniter.

I have tried the following method

$this->db->select('SUM(IF(pay_type = 1, pay_amount, 0)) mess_pay, SUM(IF(pay_type=2, pay_amount, 0)) est_pay');
$this->db->from('mess_stock');

But it throws the following output

A Database Error Occurred
Error Number: 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 'mess_pay, SUM(IF(pay_type=2, `pay_amount`, `0))` est_pay FROM (`mess_stock`)' at line 1


SELECT SUM(IF(pay_type = 1, `pay_amount`, `0))` mess_pay, SUM(IF(pay_type=2, `pay_amount`, `0))` est_pay FROM (`mess_stock`)


Filename: /var/www/college/modules/mess_fees/models/mess_fees_model.php
Line Number: 25

How can I execute the above query?

AdrienXL · Accepted Answer · 2015-02-23 09:29:54Z

2

Set the last parameter of select() to false :

$this->db->select('SUM(IF(pay_type = 1, pay_amount, 0)) mess_pay, SUM(IF(pay_type=2, pay_amount, 0)) est_pay', false);

It will prevent CI from adding ``.

Take a look at the doc : http://www.codeigniter.com/user_guide/database/active_record.html#select

$this->db->select() accepts an optional second parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks. This is useful if you need a compound select statement.

answered Feb 23, 2015 at 9:29

AdrienXL

3,0083 gold badges25 silver badges40 bronze badges

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

Comments

Роман Зыков · Accepted Answer · 2017-07-06 15:13:14Z

0

USE THIS

$summ = $this->db->query("SELECT SUM(column) FROM 
table WHERE columnid > '0' ")->result_array();
echo '<br>summ=>'.$summ[0]['SUM(answer)'];

answered Jul 6, 2017 at 15:13

Роман Зыков

6036 silver badges7 bronze badges

Collectives™ on Stack Overflow

Mysql query sum query in codeigniter

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related