CODEWITHSUNDEEP
sqlcodeignitersubquery
TShrestha
TShrestha

Reputation: 1328

How to run subquery in CI

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.

Upvotes: 1

Views: 202

Answers (2)

disha
disha

Reputation: 123

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

Upvotes: 1

Tpojka
Tpojka

Reputation: 7111

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')");

Upvotes: 1

Related Questions