php - How can I use for loop in a IN statement in this situation?

Question

I am using for loop for select in. The issue I am encounter is I have multiple value in the IN statement. I know IN(term_1, term_2) we need a period to separate each one. When I use for loop, I don't know how to append the period. First, it was inside the statement parenthesis instead of at the end.

<?php
for ($i = 0; $i <= 8; $i++) {
    $stm =$db->prepare("SELECT id FROM sign WHERE term IN (:term_$i) GROUP BY os.user_id order by COUNT(os.user_id) DESC ");

        $stm->bindParam(":term_$i", $search_text[$i]);

        $stm->execute();
    }

?>

Answer 1

Prepared statement placeholders can only represent SINGLE values.

e.g.

$foo = '1,2,3';
$db->prepare("SELECT ... WHERE foo in (:foo)");
bind(':foo', $foo);

would create a query that's interpreted as

SELECT ... WHERE foo IN ('1,2,3');

Note the quotes - your three separate numbers are now a single-valued string, and the query runs as if it had been written:

SELECT ... WHERE foo='1,2,3'

You'd have to build a dynamic statement and create as many placeholders as you have values.

foreach ($values as $val) {
     $placeholders[] = '?';
}
$sql = "SELECT ... WHERE foo IN (" . implode(',', $placeholders) . ")";
...prepare/bind other values
$stmt->execute($values);