How to write the given query in CodeIgniter

Question

I want to write the given query in codeigniter , how can I do so?

SELECT * FROM `user` WHERE `id` IN (select `provider_id` from `services`);

I gone through codeigniter's where_in() but i'm getting wrong result , here what I have done,

$this->db->select('*');
$this->db->from(USER);
$this->db->where_in('id', 'select `provider_id` from `services`');
$query = $this->db->get();
return $query->num_rows();

it is returning 0, while the query above, when run directly in phpmyadmin, returns 1 record.

http://stackoverflow.com/questions/6315511/select-in-select-query-in-codeigniter — splash58, Jul 24 '15 at 08:31
$this->db->query is easiest way if you cannot use CI active record — Shaiful Islam, Jul 24 '15 at 08:32

score 1 · Accepted Answer · edited May 23 '17 at 11:51

1

You can use this simple way $this->db->query('your query');

Please check this post, hope it will help you Using Mysql WHERE IN clause in codeigniter

edited May 23 '17 at 11:51

Community

1
1

answered Jul 24 '15 at 08:30

Suvash sarker

3,022
1
16
19

score 1 · Answer 2 · answered Jul 24 '15 at 08:57

1

You can pass direct queries in where class by passing second parameter as NULL and third parameter as FALSE

<?php 
$where = 'id IN (select `provider_id` from `services`)';
$this->db->select('*');
$this->db->from(USER);
$this->db->where($where, NULL, FALSE);
$query = $this->db->get();
return $query->num_rows();

answered Jul 24 '15 at 08:57

Sundar

4,318
6
32
56

Hello thanks for helping, I tried all the 3(Suvash, Sundar, and Vinie) answers and all of them are working. Thanks for the alternate ways... – PhpCoder Aug 12 '15 at 10:13

score 1 · Answer 3 · answered Jul 24 '15 at 11:23

1

Use $this->db->query();

$query=$this->db->query("SELECT * FROM `user` WHERE `id` IN (select `provider_id` from `services`)");
$num=$query->num_rows();
$result= $query->result();

answered Jul 24 '15 at 11:23

Vinie

2,910
1
14
29

How to write the given query in CodeIgniter

3 Answers3