CODEWITHSUNDEEP
phpcodeigniterquery-builderwhere-inin-subquery
someoneuseless
someoneuseless

Reputation: 294

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()

Upvotes: 0

Views: 67

Answers (2)

mickmackusa
mickmackusa

Reputation: 48001

Best practice recommends safely rendering the subquery, then injecting that safe subquery into the parent query.

$sub = $this->db
    ->select('lookbook_id')
    ->where('point_id', $pointid)
    ->get_compiled_select('shopify_point');

return $this->db
    ->select('shop')
    ->where_in('lookbook_id', $sub, false)
    ->get('shopify_lookbook')
    ->result_array();

Upvotes: 0

mitkosoft
mitkosoft

Reputation: 5316

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

Upvotes: 1

Related Questions