How to run subquery in CI

Question

Hello everyone I am trying to run a subquery in codeigniter. And I have done this

$this->db->where('id NOT IN (SELECT friend_id FROM friend_list WHERE user_id = '$id')');

My function looks like this

public function get_all_users()
{

    $id=$this->session->userdata['user_id'];
    $this->db->select("id,username");
    $this->db->where('id NOT IN (SELECT `friend_id` FROM `friend_list` WHERE `user_id` = '$id')');
    $this->db->where('id !=2');
    $this->db->from('users');
    $query=$this->db->get();

    if ($query->num_rows() > 0)
    {
       return $query->result();
    }

    return false;
}

but it is returning this error

    Message: syntax error, unexpected '$id' (T_VARIABLE), expecting ',' or ')'.

If i put some value in pace of $id then it gives result. How can i solve this. Please help me.

Escape quotes or use mixed case `$this->db->where("id NOT IN (SELECT friend_id FROM friend_list WHERE user_id = '$id')");` — Tpojka, Jul 29 '17 at 19:38
Codeigniter has built in where not in https://www.codeigniter.com/user_guide/database/query_builder.html?highlight=where_not_in#CI_DB_query_builder::where_not_in — , Jul 29 '17 at 21:42

disha · Answer 1 · 2017-08-16T09:30:15.383

1

First you need to include library for subquery:

$this->load->library('subquery');

Then put your query like this:

This is how we can write sub query in CI method 1:

$this->db->where('id NOT IN (SELECT `friend_id` FROM `friend_list` WHERE `user_id` = '$id')', NULL, FALSE);

OR You can also write like this: method 2:

$this->db->select('id,username')->from('users');
$sub = $this->subquery->start_subquery('where_in');
$sub->select('friend_id')->from('friend_list');
$sub->where('user_id', $id);
$this->subquery->end_subquery('id', FALSE);

you can check this reference: subquery in codeigniter active record

edited Aug 16 '17 at 09:30

answered Jul 31 '17 at 11:35

disha

123
11

Just want to clarify that if you want to use the second option you need to add this [library](http://labs.nticompassinc.com/CodeIgniter-Subqueries/) to your CI setup. Because CI doesn't have Any `$this->subquery->start_subquery` function as far I know. – always-a-learner Aug 06 '17 at 05:19
@disha the subQuery is a library which needs to be loaded before using the subquery methods – cyberrspiritt Aug 12 '17 at 11:26
@cyberrspiritt: yes. We need to add librabry for subquery. That is: $this->load->library('subquery'); – disha Aug 16 '17 at 09:28

score 1 · Answer 2 · answered Jul 31 '17 at 12:42

You forgot to escape same (single) quotes:

You should write it as:

$this->db->where('id NOT IN (SELECT `friend_id` FROM `friend_list` WHERE `user_id` = \'$id\')');

or

$this->db->where('id NOT IN (SELECT `friend_id` FROM `friend_list` WHERE `user_id` = ' . $id . ')');

or

$this->db->where("id NOT IN (SELECT `friend_id` FROM `friend_list` WHERE `user_id` = '$id')");

How to run subquery in CI

2 Answers2