phpcodeigniterquery-builderwhere-inin-subquery

SELECT WHERE IN subquery using CodeIgniter's where_in() method

I am having a problem with where_in . I am trying to get the shop name which possess the lookbook had the specific point id

$this->db->select('shop');
$this->db->from('shopify_lookbook');
$this->db->where_in(
    'lookbook_id',
    'SELECT lookbook_id
     FROM shopify_point
     WHERE point_id = $pointid'
);

The problem is the query it generate

SELECT `shop` FROM `shopify_lookbook` WHERE `lookbook_id` IN('SELECT lookbook_id FROM shopify_point WHERE point_id = 543')

It will give blank but when I try in mysql without '' in IN() like below

SELECT `shop`
FROM `shopify_lookbook`
WHERE `lookbook_id` IN(
    SELECT lookbook_id
    FROM shopify_point
    WHERE point_id = 543
)

It returns the shop name that I want. How can I erase '' in $this->db->where_in()

Solution

  • You might use where instead and to construct your IN clause there:

    $this->db->where('lookbook_id IN (SELECT lookbook_id FROM shopify_point WHERE point_id = $pointid)', NULL, FALSE);