CODEWITHSUNDEEP
phpcodeigniter
Sajeev C
Sajeev C

Reputation: 1538

CodeIgniter Active Records to compare two columns of same mysql table

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?

Upvotes: 3

Views: 6884

Answers (3)

Binar Web
Binar Web

Reputation: 943

In Codeigniter 3:

$this->db->where('amount', 'discount', false);

the third parameter will disable escaping.

Upvotes: 0

Disha V.
Disha V.

Reputation: 1864

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

Upvotes: 8

Shailesh Katarmal
Shailesh Katarmal

Reputation: 2785

Try this:

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

Upvotes: 0

Related Questions