CodeIgniter Active Records比较同一个mysql表的两列

Question

I'll get straight into the point.

My table looks something like this

CREATE TABLE user_orders(id INT(11), o_name VARCHAR(60), amount INT(10), discount INT (10), overall INT(10));
INSERT INTO user_orders(id,o_name,amount,discount,overall) VALUES (1,'first', 10,0,10);
INSERT INTO user_orders(id,o_name,amount,discount,overall) VALUES (2,'second', 20,20,40);
INSERT INTO user_orders(id,o_name,amount,discount,overall) VALUES (3,'third', 0,0,0);
INSERT INTO user_orders(id,o_name,amount,discount,overall) VALUES (4,'fourth', 40,40,80);

And this is what I'm tryna obtain:

SELECT * FROM user_orders
WHERE amount=discount
AND amount!=0 AND discount!=0;

SQL FIDDLE

I am trying to fetch data from table when the fields amount and discount are same and are not equal to zero.

So using CI, I wrote this

    $this->db->select('count( id ) AS total_discounted',FALSE);
    $this->db->where('amount','discounts');     
    $this->db->where('discounts !=',0);
    $this->db->where('amount !=',0);

    $results=$this->db->get('user_orders');
    echo $this->db->last_query();

which produces query

SELECT * FROM user_orders
WHERE amount='discount'
AND amount!=0 AND discount!=0;

where amount gets compared to THE STRING 'discount' and not the field discount.

How to achieve this kinda thing using CI? Any clues?

Answer 1

Try $this->db->where('amount = discount');