My array contains multiple integers. Array is @group_id. Suppose, it contains three elements such as 12,45,87. I want to pass these to a select statement as below.
select * from groups where id in (@group_id) // should get all the ids inside it.
Currently I am not getting the values in the query.
You could use something like
local $" = ",";
before the query if id's are numbers, but that leaves you vulnerable to sql injection attacks, so use queries with ? placeholders,
my $placeholders = join ",", ("?") x @group_id;
my $sql = "select * from groups where id in ($placeholders)";
# $sth prepare..
$sth->execute(@group_id);
Your query will look for a string value, such as '1,2,3' rather than three separate values.
If you are constructing the query in a string, you can directly insert the values in the stirng:
where id in (".@group_id.") . .
However, you need to be careful, because this can open you up to SQL injection attacks.
If your table is not very big (or you are not concerned about performance), you can use find_in_set():
select *
from groups
where find_in_set(id, @group_id) > 0;
