How to use IN dynamically with mysqli prepare statement

Question

I was trying to use IN with mysqli prepare statment

$user_in = "'28','22'";
$stmt = $this->connection->prepare("SELECT `id` FROM `$this->table_name` WHERE `user_id` IN (?) ");         
            if($stmt){
                $stmt->bind_param('s',$user_in);
                if($stmt->execute()){
                     $result = $stmt->get_result();
                     if($result !== false && $result->num_rows >= 1){
                        $row = $result->fetch_all(MYSQLI_ASSOC);
                        $stmt->close();
                        var_dump($row);
                     }
                }
            }
            echo $this->connection->error;
            return false;

But the approach above is not able to fetch any result sets

Answer 1

Placeholders represent a SINGLE value. If you have a variable and placeholder-using query:

$var = '1,2,3';
SELECT ... WHERE foo IN (?)

then the query will be executed as the SQL had literally been

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

and your 3 separate csv values will be treated as a single monolithic string.

IN clauses are one place where placeholders are somewhat useless, since you have dynamically build up a string with as many placeholders as you have values, e.g.

$vals = array(1,2,3);
$placeholders = '?' . str_repeat(',?', count($vals) - 1);


$stmt = $db->prepare("SELECT ... WHERE foo IN ($placeholders)");
foreach($vals as $i => $val) {
    $stmt->bind($i, $vals[$i]);
}

and then