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

Question

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

you dont need to use IN when using where_in $this->db->where_in('lookbook_id', $pointid); — user1805543
– user1805543, Commented Jan 30, 2020 at 10:20
Food for thought regarding NOT using a subquery inside of WHERE IN(): CodeIgniter SELECT query to return qualifying rows with their highest value in a specific column — mickmackusa
– mickmackusa ♦, Commented Jun 3 at 6:24

mitkosoft · Accepted Answer · 2020-01-30 10:17:12Z

1

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

answered Jan 30, 2020 at 10:17

mitkosoft

5,3361 gold badge15 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mickmackusa · Accepted Answer · 2024-09-01 02:15:29Z

0

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

answered Sep 1, 2024 at 2:15

mickmackusa♦

49.2k13 gold badges98 silver badges165 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related