Codeigniter equivalent of this mysql query

Question

I am trying to get a grasp on the CI database helper/class/lib. And the core basics I can do no problem. But I am so used to straight writing of queries rather than using ORM's that its a little confusing. Anyway..

I Have this query

SELECT * FROM my_table 
  WHERE ((a_id = xx OR b_id = xx) AND (a_id = zz OR b_id = zz)) 
  AND active = 1

Basically I have a table where I have 2 users associated with one another, but multiple users so I need to make sure the connection is there and specifically between the 2 where the active flag is what it is.

However Not sure how to handle that query in CI's ORM so I am seeking some guidance on the issue.

score 1 · Accepted Answer · answered Oct 04 '12 at 06:12

1

Try the following code

$this->db->select('*');
$this->db->where('(a_id = "xx" or b_id = "xx")';
$this->db->where('(a_id = "zz" or b_id = "zz")';
$this->db->where('active', '1');

answered Oct 04 '12 at 06:12

iLaYa ツ

3,887
3
27
45

I think Active record is better since it's sanitize the query. – Shaolin Oct 04 '12 at 06:26
`$this->db->Select('*')` is redundant. By default, the Active Record class will select all fields. – Kemal Fadillah Oct 04 '12 at 06:35

score 1 · Answer 2 · edited May 23 '17 at 12:18

1

Look at this answer of mine. This will give you a fairly good idea how to use subqueries or this kind of situations in active record

How can I rewrite this SQL into CodeIgniter's Active Records?

edited May 23 '17 at 12:18

Community

1
1

answered Oct 04 '12 at 06:32

Muhammad Raheel

19,645
7
66
101

score 1 · Answer 3 · answered Oct 04 '12 at 15:44

If you don't want to use active records you can do it this way

$this->load->database();
$sql = "SELECT * FROM mytable WHERE visible = 't' ORDER BY symbol";
$query = $this->db->query($sql);
return $query->result();

See http://codeigniter.com/user_guide/database/queries.html

Codeigniter equivalent of this mysql query

3 Answers3